EXCEL FEED: Re: [ExcelVBA] Macro can't resize chart on Protected Sheet

This approach isn't what I'd normally recommend, but,
I get the impression that your decision to add protection is a matter of "convenience" rather than security?

As an alternative, you COULD create your own rudimentary sheet protection.
What I've done in the past is:
Create a Public Variable called something like:

    Public PrevSel as Range

Then, create a SelectionChange event in which the macro checks the "locked" property of the cell. 
If the selected cell is "locked", then it re-selects the "previously selected cell".

If the cell is NOT locked, then it saves this new selection as the "previously selected cell".
'-----------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Targ As Range
    For Each Targ In Target
        If (Targ.Locked = True) Then
            On Error Resume Next
            PrevSel.Select
            On Error GoTo 0
            Exit Sub
        End If
    Next Targ
    Set PrevSel = Target
End Sub
'------------------------------------

This type of protection is easily bypassed by disabling events using:

    Application.EnableEvents = False

(which you would have to use to be able to work on the file yourself!)

The nice thing about this approach is that you can actually make certain cells  accessible by some users and not others.
and you can make CHANGE events so that some users can select the cells and change the formatting, but cannot change the VALUES.

You can get pretty detailed.

Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------

    From: "noskosteve@yahoo.com [ExcelVBA]" <ExcelVBA@yahoogroups.com>
 To: ExcelVBA@yahoogroups.com 
 Sent: Thursday, October 8, 2015 12:25 AM
 Subject: [ExcelVBA] Macro can't resize chart on Protected Sheet
  

 
 Hi there community,
 
Summary: 
  Macro bombs when it selects an axis on UN-locked chart on PRO-tected sheet. I can't seem to effectively Unlock the chart (even though it shows as unlocked) to allow axis changes. 
 
 I must UNprotect the sheet temporarily.  This works, but seems crude....
 
Detail:
Been away from VBA for a while, but ... 
I now want to protect a sheet (I made some time ago) that has some cells the user can select and change and some they can't select or change.  That all works A-OK whether the sheet is protected, or not.
 
There is a chart (a crude map) which I can resize with macros that simply changes both axes.
That works A-OK when the sheet is UN-protected.
 
However, when the sheet is PROprotected and I run (button in sheet) a macro that changes the axes to resize the chart (Zoom in, or out) )  errors on the line (whole macro below):
         ActiveChart.Axes(xlValue).Select 
 
With Run Time Error  (large number)
Method 'Select' of Object 'axis' failed.
 
UNprotecting the sheet, then:
Doing a   CTRL Select  (per Help)  of the whole Chart adds the "Chart Tools" at the very top of Excel.  This has three new tabs: "Design, "Layout", and "Format".
 
On the Format tab, in the Size group, clicking the   Dialog Box Launcher (little down-left arrow)  next to Size, opens the "Size and Properties" dialog.
In the Properties tab it shows the locked checkbox UN-checked.
 
If I then just select one of the Axes of the chart, it also shows as UN-locked.
 
Enabling the "Select Objects Arrow Cursor" and selecting every bleepin' thing on the chart (I have some additional text) including the chart itself, The properties tab in the Size and Properties Dialog box does not have the Print Objects or Lock Objects options at all... It only thas he Radio buttons: "Resize With Chart" and "Do Not Resize With Chart"
 
It works if I have the macro UNprotect the sheet first, then RE-protect when done, but is there a better way??
 
 
Ideas???
 
 
Also, I forgot what the UserInterfaceOnly = False does on the Protect ...
 
-- 
Regards, Steve N.
 
Here's the code I obviously recorded... some time ago:
 
-----------------------------------------------------
Sub Zoom_Out()
'
' Zoom_Out Macro
' Macro recorded 12/1/02 by  usename
'
'
    Application.ScreenUpdating = False
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue).Select    ' <<<<<<<<<<<<<<<<<<<<  ERROR HERE
    With ActiveChart.Axes(xlValue)
        .MinimumScale = 41.83333333
        .MaximumScale = 42.83333333
        .MinorUnit = 0.033333333
        .MajorUnit = 0.166667
        .Crosses = xlCustom
 !          .CrossesAt = 35
        .ReversePlotOrder = False
        .ScaleType = xlLinear
    End With
    ActiveChart.Axes(xlCategory).Select
    With ActiveChart.Axes(xlCategory) '
        .MinimumScale = 87.8333
        .MaximumScale = 89
        .MinorUnit = 0.033333333
        .MajorUnit = 0.1666667
        .Crosses = xlCustom
        .CrossesAt = 85
        .ReversePlotOrder = True
        .ScaleType = xlLinear
    End With
    Application.ScreenUpdating = True
    Application.Cursor = xlDefault
End Sub

__._,_.___

Posted by: Paul Schreiner <schreiner_paul@att.net>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Visit Your Group