none
ActiveChart error on protected worksheet RRS feed

  • Question

  • I want to protect a worksheet to allow access to only specific cells.  Those cells are the data for the chart.  The following code works fine in the unprotected sheet:

    Sub CopyMax()
        Dim NewMax As Double   
        NewMax = ActiveSheet.Range("D16").Value
        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.Axes(xlValue).MaximumScale = NewMax
        ActiveChart.ChartTitle.Text = ActiveSheet.Range("D15").Value
        ActiveSheet.Range("B6").Value = ActiveSheet.Range("D16").Value
        ActiveSheet.Range("B1").Value = ActiveSheet.Range("D17").Value
    End Sub

    As soon as I protect the worksheet, the line setting the value for MaximumScale causes an error: "Method 'MaximizeScale' of object 'Axis' has failed". I have tried checking every box in the Protect Sheet dialog, allowing the users to do any of the options listed, and the same error is raised. I have set the chart's Locked property to False on its property page and also in code, same result.

    Why is this happening?  How can I protect the worksheet and run this macro?

    Thanks for any help.


    MW

    Saturday, June 16, 2012 12:52 PM

Answers

  • This is a bug in Excel 2007 and 2010 - it used to work fine in Excel 2003 and before.

    You can unprotect the sheet at the beginning of the macro, and protect it again at the end.


    Regards, Hans Vogelaar

    • Marked as answer by MWulfe1 Saturday, June 16, 2012 4:13 PM
    Saturday, June 16, 2012 3:48 PM

All replies

  • This is a bug in Excel 2007 and 2010 - it used to work fine in Excel 2003 and before.

    You can unprotect the sheet at the beginning of the macro, and protect it again at the end.


    Regards, Hans Vogelaar

    • Marked as answer by MWulfe1 Saturday, June 16, 2012 4:13 PM
    Saturday, June 16, 2012 3:48 PM
  • Not exactly what I was hoping to hear, but thanks for the information.  I think your kluge will work.

    MW

    Saturday, June 16, 2012 4:14 PM