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: 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:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar