Rabu, 07 Oktober 2015

[ExcelVBA] Macro can't resize chart on Protected Sheet


 Hi there community,



  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....



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):



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??





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
    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: noskosteve@yahoo.com
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)
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:

More free tutorials and resources available at:




Tidak ada komentar:

Posting Komentar