When macro tries to modify a chart in a protected

Question

Sheet1 is protected from Workbook_Open() using:

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

A macro is trying to change the font size of Chart1's Axes label, using the following statement:

ThisWorkbook.Worksheets("Sheet1").ChartObjects("Chart1").Chart. _ 
             Axes(xlCategory).TickLabels.Font.Size = 10

and I get the following error:

Error: 1004.

Unable to set the Size property of the font class.

When I use the following workaround, the problem does not occur anymore:

ThisWorkbook.Worksheets("Sheet1").Unprotect "Pass1"
ThisWorkbook.Worksheets("Sheet1").ChartObjects("Chart1").Chart. _
             Axes(xlCategory).TickLabels.Font.Size = 10
ThisWorkbook.Worksheets("Sheet1").Protect "Pass1"

But I want to avoid using Unprotect/Protect sheet that's why I choose to use

ThisWorkbook.Worksheets("Sheet").Protect Password:="Pass1", _
         UserInterfaceOnly:=True,

which seems not to work for the particular case.

Answer 1

Worksheet.Protect has a variety of arguments you can use (see here). In particular, try adding the DrawingObjects:=False argument to your protecting code

ThisWorkbook.Worksheets("Sheet1").Protect Password:="Pass1", _ 
             UserInterfaceOnly:=True, DrawingObjects:=False

which will explicitly allow you to modify the charts.

It appears that UserInterfaceOnly:=True doesn't truly give VBA full permission as mentioned here.