Can’t Set Minimum Scale of a Chart when Worksheet is Protected

[Nouveau]

I have a macro in a protected worksheet that performs some calculations and updates a chart when the worksheet is activated by clicking on its tab. Within this macro, I set the minimum value of the chart's X-axis using the following command:

ActiveSheet.ChartObjects("Chart 3").Chart.Axes(xlCategory).MinimumScale = WorksheetFunction.Min(Range("U5:U7"))

Everything works perfectly when the worksheet is unprotected. When I protect the sheet, the code stops at this line and I get an error that says, “Method ‘MinimumScale’ of Object ‘Axis’ failed”

I tried to give the user permission to Edit Objects when the sheet is protected, but that doesn’t seem to work.

I suppose I could unprotect the sheet at the beginning of the macro and then protect it again at the end, but I hate to open a vulnerability. Is there a better way to do this, please? Thank you.

 

[Domenic]

This will leave the user interface protected while allowing your macro to run. Then, once finished, it protects both the user interface and macros.

    ThisWorkbook.Worksheets("Sheet1").Protect Password:="YourPassword", UserInterfaceOnly:=True

    'your code here
   
    ThisWorkbook.Worksheets("Sheet1").Protect Password:="YourPassword", UserInterfaceOnly:=False

Hope this helps!

 

[Nouveau]

Yes, that worked Domenic. In my case, I put these just before and just after the ActiveSheet.ChartObjects line, leaving the rest of the sheet protected for most of the macro execution.

It's interesting that I have to do this when I can make other changes to the sheet while it's protected. This is the only line giving me trouble.

Thank you for your help.

 

[Domenic]

  ThisWorkbook.Worksheets("Sheet1").Protect Password:="YourPassword", UserInterfaceOnly:=True

With the above line, the worksheet remains protected throughout the macro, even when executing your line of code. It's only macros that are not protected.

    ThisWorkbook.Worksheets("Sheet1").Protect Password:="YourPassword", UserInterfaceOnly:=False

With the above line, macros become protected again so that now both the user interface and macros are protected.

 

[Nouveau]

I didn't know that. Thank you, Domenic.

 

[Nouveau]

I didn't know about the UserInterfaceOnly option and learned something from you. Thanks.

 

[Domenic]

That's great, glad I could help.

Cheers!