Hi Excel-friends,
I have a chart in a protected worksheet and I need to modify the scaleMin and ScaleMax property, very frequently. The chart is showing online data from an OPC server and is updated two times in a second. Thats why I cam not unprotect the sheet, do the action and protect it afterwards.
I am looking for a way, to modify the properties while the sheet and the chart are protected.
What I have:
Look inside my code:
Private Sub Workbook_Open()
Dim Sheet1 As Worksheet
Set Sheet1 = ActiveWorkbook.Sheets(1)
Sheet1.Protect Password:="abc", UserinterfaceOnly:=True, DrawingObjects:=True, Contents:=True
End Sub
Private Sub CommandButton1_Click()
Dim Sheet1 As Worksheet
Dim MyChartObject As ChartObject
Dim MyChart As Chart
Dim MyAxes As Axes
Set Sheet1 = ActiveWorkbook.Worksheets(1)
Set MyChartObject = Sheet1.ChartObjects(1)
Set MyChart = MyChartObject.Chart
Set MyAxes = MyChart.Axes
MyAxes(xlCategory).MinimumScale = 1
MyAxes(xlCategory).MaximumScale = 100
End Sub
Xcel_Charts.xlsm
I get a runtime error (80004005), telling me that the method minimumScale has failed. In the moment I use Excel 2003 but later I want to run it in Excel2010.
If I remove the protection of the sheet, it is running well.
Excel Charts.JPG
Please help me.
Bookmarks