Charts from 2003 not working in 2007 1

[Thankyou12]

Hello,

I'm new to Excel programming. I inherited a report created using Excel 2003. The charts on the report have a scale that changes based on a combo box value. It works fine in 2003 but in 2007 the scale remains fixed. The line graph on the charts falls off the grid for certain combo box values as the scale dosen't change. I have enabled macros and I don't get any error messages. Below is the code. Any help is appreciated. Thanks!


Code:
Private Sub ComboBox1_Change()
On Error GoTo 1
With Worksheets("Supply").ChartObjects(2).Chart.Axes(xlValue)
.MaximumScale = Range("AF2").Value
.MinimumScale = Range("AF3").Value
End With
With Worksheets("Supply").ChartObjects(1).Chart.Axes(xlValue)
.MaximumScale = Range("AE2").Value
.MinimumScale = Range("AE3").Value
End With

 

[kjv1611]

Have you saved it as a Macro-Enabled workbook in Excel 2007, just to see? For older versions, you didn't have to do that, but in 07, you do.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Thankyou12]

Yes. Macros are enabled. The sheet was protected. When I unprotect the sheet the graphs look fine. It must be something to do with the protection. How can I protect the sheet and get the graphs to work?

 

[kjv1611]

You could do it in your VBA code. When the update needs to occur, have the option added to your code to turn protection off just before the update, and then re-enable protection. Of course, if you have a password on it, you'll have to have it in plain text in your VBA code, or else store it in some other location which can be accessed by the VBA code.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Thankyou12]

I have the following code:

Private Sub Workbook_Open()
Worksheets("Supply").Protect Password:="Secret", _

UserInterFaceOnly:=True
End Sub

I get a compile error "Expected: named Parameter

 

[kjv1611]

Any reason for having the extra line between Worksheets... and UserInterFaceOnly.... ?

Try taking that one line out, and see if that fixes it.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Thankyou12]

I coded something different:

Private Sub ComboBox1_Change()
'Unprotect worksheet that contains the charts
Worksheets("Supply").Unprotect Password:="Secret"
On Error GoTo 1
With Worksheets("Supply").ChartObjects(2).Chart.Axes(xlValue)
.MaximumScale = Range("AF2").Value
.MinimumScale = Range("AF3").Value
End With
1:
'Protect the worksheet that contains the charts
Worksheets("Supply").Protect Password:="Secret"
End Sub

This seems to work ok. One question - If the code errors out or is stopped in the middle would that leave the sheet unprotected?

 

[kjv1611]

To handle that, you'd need to include an error handler:

Private Sub ComboBox1_Change()
[b]On Error GoTo ErrHandle[/b]
[green]'Unprotect worksheet that contains the charts[/green]
  Worksheets("Supply").Unprotect Password:="Secret"
[green][b]'Don't need this one, if put in most common place - very first line.[/b]
  'On Error GoTo 1 - 'Changed to ErrHandle[/green]
  With Worksheets("Supply").ChartObjects(2).Chart.Axes(xlValue)
      .MaximumScale = Range("AF2").Value
      .MinimumScale = Range("AF3").Value
  End With

[b]ExitSub:[/b]
[green]'Protect the worksheet that contains the charts[/green]
  Worksheets("Supply").Protect Password:="Secret"
[b]  Exit Sub

ErrHandle:
  MsgBox Err.Code & " " & Err.Description
  Resume ExitSub[/b]
End Sub

Well, you sort of had an error handler in there, in that you said, on error go here... but

--

"If to err is human, then I must be some kind of human!" -Me

 

[Thankyou12]

Hey Thanks kjv1611. I think I have it all set.

 

[xlhelp]

Thankyou12, I was wondering. Wouldn't it be really nice kjv1611 got one of those purple things? I mean besides the thanks!

A man has only two choices: He can be right or he can be happy.

 

[Thankyou12]

done!