vba error for Chart on protected sheet

Question

Hi Gang

My client is working in 2010 and has a very complex workbook. On one sheet there is a chart that picks data from another sheet. The User needs to be able to select the time range for the chart from some dropdown boxes (validation data) from the other sheet.

My code tells the chart to update it's data based on what has been selected in the dropdowns.

My problem occurs when the sheet is protected. Selecting a value in either dropdown results in the following error:

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

The chart and both of the dropdowns are unlocked. In the protection dialog box, I chose Select Unlocked Cells and Edit Objects.

My code and where it breaks are below:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target = Range("S6") Or Target = Range("S7") Then

        ActiveSheet.ChartObjects("PRC_Chart").Activate         **Highlighted Debug Line***

    With ActiveChart.Axes(xlCategory)

        If Range("S6").Value <> "" Then .MinimumScale = Range("S6").Value

        If Range("S7").Value <> "" Then .MaximumScale = Range("S7").Value

    End With

    End If

End Sub

There is a possibility that this sheet will be password protected, so I am hoping that there is a solution that is not just to unprotect/protect the sheet.

Answer 1

well it's good and bad news.

good news is you can remove the 1004 error by not activating the chart.

With ActiveSheet.ChartObjects("PRC_Chart").Chart.Axes(xlCategory)

But this just takes you to the next error when actually attempting to alter the scale values.

-2147467259

Method 'MaximumScale' of object 'Axis' failed

So looks like you will need to unprotect/protect.

A possible alternative is to move the chart off the protected sheet and replace it with a linked picture that display dynamic image of chart. You code can then alter the chart and the image will automatically update.

http://chandoo.org/wp/2010/10/19/how-to-use-picture-links/

Answer 2

Thanks Andy, Paste Link appears when I copy a block of text (RHS below), but not for a chart (LHS below).  Could there be something in my settings which causes this?

Answer 3

@alexJJJ,

In xl2010 copy and then press the Paste button on ribbon. The Paste Link option is icon bottom right.

Answer 4

I've been all over the web looking for solutions to the same problem.. rescaling the axis in a chart on a protected sheet. No other solutions worked so far, and this was the most recent post I found.

The dynamic image link is a nice suggestion ... but it seems to be referring to Excel 2007 and I dont have the option to "Paste as Picture Link" in Excel 2010 (V14.07106.5003).

Is that option hidden somewhere that I can't see at present. ?

Using the Excel Help file search terms "picture link" provides no help.

Answer 5

Well alrighty then.

Thanks Andy, I never would have thought of a Picture Link.

That should work.

Cheers!