I have a chart on a page that needs to have the Chart Axis Title updated with the discount rate for the NPV values contained on the chart. I'd like to update this Axis Title without unlocking the sheet that it is on as I don't want to embed the password for the sheet in the VBA or in the spreadsheet file anywhere.

For other shapes on the same locked sheet I have been able to simply set the Shapes(x).locked property to false, which allowed the properties (e.g. left or top) of the shape to be updated without locking the sheet. When I try to do this on the shape that is the chart, the shapes(x).locked property is updated to false, but the update of the AxisTitle fails.

The update of the AxisTitle.Caption property succeeds with no problem when the sheet is unlocked. Therefore, the failure of the update is related to the sheet being locked. Leaving the sheet unlocked is not an option and I don't want to embed the password in the code or workbook anywhere as would be necessary to Sheet.Unprotect->Update AxisTitle->Sheet.Protect.

The code that I'm using follows:

Please Login or Register  to view this content.
Is the chart picking up a locked status from some other object (e.g. ChartObjects)? I've actually tried to set both the .Shapes(x).locked property and the ChartObjects(x).locked property to False. So, I'm pretty sure that it isn't ChartObjects. I'm wondering if someone else knows why the update of the "AxisTitle.Caption property is failing when the sheet is locked.

Thanks,

dalelengle