sjgrey
asked on
Setting graph label in protected sheet - VBA Excel 2007 SP2
I have a chart in a protected worksheet where a macro sets a data label using the following code
The sheet is protected on opening the workbook with UserInterFaceOnly to allow macros to update the protected cells using the code (password replaced by asterisks for this post)
With the sheet unprotected the code runs fine. When it is protected though, the worksheet cells can be accessed by the code but setting the dala label fails on the line "DataLabel.Text = s" with the error message "Method 'Text' of object 'DataLabel' failed". See attached screen capture for runtime error. Error-when-updating-chart-data-l.docx
The chart has been upprotected as an object by unchecking the Locked option in the Chart tools / Format / Size / Properties tab
Any ideas on how to get past this? I am releasing the workbook to senior managers and need to make it fairly well tamper proof while retaining the VBA functionality
Sub SetLabel()
Dim s As String
With ActiveSheet.ChartObjects(1).Chart
With .SeriesCollection(3).Points(1)
s = Range("B2").Value
.ApplyDataLabels
.DataLabel.Text = s
End With
End With
End Sub
The sheet is protected on opening the workbook with UserInterFaceOnly to allow macros to update the protected cells using the code (password replaced by asterisks for this post)
Private Sub Workbook_Open()
Application.EnableEvents = True
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Protect Password:="*********", UserInterFaceOnly:=True
Next wSheet
End Sub
With the sheet unprotected the code runs fine. When it is protected though, the worksheet cells can be accessed by the code but setting the dala label fails on the line "DataLabel.Text = s" with the error message "Method 'Text' of object 'DataLabel' failed". See attached screen capture for runtime error. Error-when-updating-chart-data-l.docx
The chart has been upprotected as an object by unchecking the Locked option in the Chart tools / Format / Size / Properties tab
Any ideas on how to get past this? I am releasing the workbook to senior managers and need to make it fairly well tamper proof while retaining the VBA functionality
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER