Protect worksheet cells but still allow macro to edit chartproperties

[rrj]

I have a worksheet with calculations and charts.
The calculations refer to data in a separate worksheet that can be
overwritten by loading new data from a csv file.
I've written a macro to do this.
When I load the data I rescale the charts from the macro to best
visualise the new data set
There are then some parameters next to the charts which the user can
modify to optimise the result.
Everything works fine, but now I want to protect the calculations.
I can choose which cells to lock/unlock and then protect the sheet.

The chart axis scales can still be edited by the user, but the problem
is that the macro to rescale the chart then fails.

In its simplest form it looks like this

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).MinimumScale = "something"

I get the message Run-time error '1004', application-defined or object-
defined error.
Its the activate that fails, not the scale change.
Its there any way of working around this?

Thanks
Richard

 

[Per Jessen]

Hi Richard

You have to unprotect first:

pWord = "JustMe"
ActiveSheet.Unprotect Password:=pWord
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).MinimumScale = "something"
ActiveSheet.Protect Password:="pWord"

Hopest this helps

 

[rrj]

Hi Richard

You have to unprotect first:

pWord = "JustMe"
ActiveSheet.Unprotect Password:=pWord
ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue).MinimumScale = "something"
ActiveSheet.Protect Password:="pWord"

Hopest this helps

Thanks Per,

That does help, but is there any way to unprotect just the chart?

Richard