VBA error activating a chart on a protected worksheet

[tj4242]

I have a worksheet that has cells and charts on it. When the worksheet is unprotected everything works perfect. When I try to protect some cells, the VBA crashes on this line

ActiveSheet.ChartObjects(chartname).Activate

The error is Run-time error '1004'. Application-defined or object defined error.

If I enter the password and unprotect the sheet, everything goes back to working. Any suggestions?

 

[aaewalsh]

This error is likely occurring because the ChartObjects collection is not accessible when the sheet is protected. When a sheet is protected, certain actions, such as modifying or activating objects, are not allowed by default.​

One solution would be to unprotect the sheet before activating the chart, and then protect it again afterwards. This can be done using the Unprotect and Protect methods of the Worksheet object, like this:

ActiveSheet.Unprotect (password)
ActiveSheet.ChartObjects(chartname).Activate
ActiveSheet.Protect (password)

Note that you would need to replace "password" with the actual password used to protect the sheet.

Another option is to use the AllowFormattingCells and AllowUsingObjects properties of the Protect method to allow specific actions like formatting cells and using chart objects even when the sheet is protected.

ActiveSheet.Protect(password, AllowFormattingCells:=True, AllowUsingObjects:=True)

You should also check that the chartname exists and that you have the correct name and that you are pointing to the correct chart.

Hope this helps!

 

[tj4242]

Those are all great suggestions. Thank you.

 

[tj4242]

BTW, this worked perfectly.

ActiveSheet.Unprotect (password)
ActiveSheet.ChartObjects(chartname).Activate
do other stuff
ActiveSheet.Protect (password)

 

[aaewalsh]

Super. Can you close as a solution please. Anything else let me know