Excel 2003 code to use instead of ChartObjects

B

Breck

This code works in Excel 2007 but the section 'To update user value
for CrossesAt doesn't work in Excel 2003 run time error '1004' unable
to get ChartObjects of the worksheet class is displayed. What changes
need to be made to get this code to work in Excel 2003. If the code
could be modified to the change the crosses at value only if the user
has change the field on row 20 would be perfect but I just started
learning VBA the last couple of months and haven't figured how how yet



Private Sub worksheet_activate()
Application.ScreenUpdating = False

'In case sheets are grouped
Sheet4.Select

On Error GoTo Ungroup

'To Unprotect this sheet if protected
If Me.ProtectContents = True Then
ActiveSheet.Select
ActiveSheet.Unprotect
Else
End If

'To Update user value for CrossesAt value
With ActiveSheet
If .ChartObjects("Chart 1").Chart.Axes(xlValue).CrossesAt
<> .Range("I20") Then
.ChartObjects("Chart 1").Chart.Axes(xlValue).CrossesAt
= .Range("I20")
End If
If .ChartObjects("Chart 2").Chart.Axes(xlValue).CrossesAt
<> .Range("J20") Then
.ChartObjects("Chart 2").Chart.Axes(xlValue).CrossesAt
= .Range("J20")
End If
If .ChartObjects("Chart 3").Chart.Axes(xlValue).CrossesAt
<> .Range("K20") Then
.ChartObjects("Chart 3").Chart.Axes(xlValue).CrossesAt
= .Range("K20")
End If
If .ChartObjects("Chart 4").Chart.Axes(xlValue).CrossesAt
<> .Range("P20") Then
.ChartObjects("Chart 4").Chart.Axes(xlValue).CrossesAt
= .Range("P20")
End If
If .ChartObjects("Chart 5").Chart.Axes(xlValue).CrossesAt
<> .Range("Q20") Then
.ChartObjects("Chart 5").Chart.Axes(xlValue).CrossesAt
= .Range("Q20")
End If
If .ChartObjects("Chart 6").Chart.Axes(xlValue).CrossesAt
<> .Range("R20") Then
.ChartObjects("Chart 6").Chart.Axes(xlValue).CrossesAt
= .Range("R20")
End If
If .ChartObjects("Chart 7").Chart.Axes(xlValue).CrossesAt
<> .Range("S20") Then
.ChartObjects("Chart 7").Chart.Axes(xlValue).CrossesAt
= .Range("S20")
End If
If .ChartObjects("Chart 8").Chart.Axes(xlValue).CrossesAt
<> .Range("Z20") Then
.ChartObjects("Chart 8").Chart.Axes(xlValue).CrossesAt
= .Range("Z20")
End If
If .ChartObjects("Chart 9").Chart.Axes(xlValue).CrossesAt
<> .Range("AA20") Then
.ChartObjects("Chart 9").Chart.Axes(xlValue).CrossesAt
= .Range("AA20")
End If
End With

'This hides all rows with zero values
Range("AI1:AI262").AutoFilter Field:=1, Criteria1:="<>0"

'This resets protection
If Me.ProtectContents = False Then
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True, AllowFormattingColumns:=True, AllowFiltering:=True
Else
ActiveSheet.Unprotect
End If

On Error GoTo 0

'The macro is done. Use Exit sub, otherwise the macro
'execution WILL continue into the error handler
Exit Sub

Ungroup:
MsgBox "Please ungroup sheets"

End Sub
 
J

Jim Cone

In older versions of XL the CrossesAt property does not work on 3D and Radar charts.
Also you could try...
..Chart.Axes(xlValue).Crosses = xlCustom before using the CrossesAt property.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Breck"
wrote in message
This code works in Excel 2007 but the section 'To update user value
for CrossesAt doesn't work in Excel 2003 run time error '1004' unable
to get ChartObjects of the worksheet class is displayed. What changes
need to be made to get this code to work in Excel 2003. If the code
could be modified to the change the crosses at value only if the user
has change the field on row 20 would be perfect but I just started
learning VBA the last couple of months and haven't figured how how yet



Private Sub worksheet_activate()
Application.ScreenUpdating = False

'In case sheets are grouped
Sheet4.Select

On Error GoTo Ungroup

'To Unprotect this sheet if protected
If Me.ProtectContents = True Then
ActiveSheet.Select
ActiveSheet.Unprotect
Else
End If

'To Update user value for CrossesAt value
With ActiveSheet
If .ChartObjects("Chart 1").Chart.Axes(xlValue).CrossesAt
<> .Range("I20") Then
.ChartObjects("Chart 1").Chart.Axes(xlValue).CrossesAt
= .Range("I20")
End If
If .ChartObjects("Chart 2").Chart.Axes(xlValue).CrossesAt
<> .Range("J20") Then
.ChartObjects("Chart 2").Chart.Axes(xlValue).CrossesAt
= .Range("J20")
End If
If .ChartObjects("Chart 3").Chart.Axes(xlValue).CrossesAt
<> .Range("K20") Then
.ChartObjects("Chart 3").Chart.Axes(xlValue).CrossesAt
= .Range("K20")
End If
If .ChartObjects("Chart 4").Chart.Axes(xlValue).CrossesAt
<> .Range("P20") Then
.ChartObjects("Chart 4").Chart.Axes(xlValue).CrossesAt
= .Range("P20")
End If
If .ChartObjects("Chart 5").Chart.Axes(xlValue).CrossesAt
<> .Range("Q20") Then
.ChartObjects("Chart 5").Chart.Axes(xlValue).CrossesAt
= .Range("Q20")
End If
If .ChartObjects("Chart 6").Chart.Axes(xlValue).CrossesAt
<> .Range("R20") Then
.ChartObjects("Chart 6").Chart.Axes(xlValue).CrossesAt
= .Range("R20")
End If
If .ChartObjects("Chart 7").Chart.Axes(xlValue).CrossesAt
<> .Range("S20") Then
.ChartObjects("Chart 7").Chart.Axes(xlValue).CrossesAt
= .Range("S20")
End If
If .ChartObjects("Chart 8").Chart.Axes(xlValue).CrossesAt
<> .Range("Z20") Then
.ChartObjects("Chart 8").Chart.Axes(xlValue).CrossesAt
= .Range("Z20")
End If
If .ChartObjects("Chart 9").Chart.Axes(xlValue).CrossesAt
<> .Range("AA20") Then
.ChartObjects("Chart 9").Chart.Axes(xlValue).CrossesAt
= .Range("AA20")
End If
End With

'This hides all rows with zero values
Range("AI1:AI262").AutoFilter Field:=1, Criteria1:="<>0"

'This resets protection
If Me.ProtectContents = False Then
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True, AllowFormattingColumns:=True, AllowFiltering:=True
Else
ActiveSheet.Unprotect
End If

On Error GoTo 0

'The macro is done. Use Exit sub, otherwise the macro
'execution WILL continue into the error handler
Exit Sub

Ungroup:
MsgBox "Please ungroup sheets"

End Sub
 
J

Jon Peltier

I tested this with a 2D column chart, and it worked as written in 2003.
Strictly speaking you should use .Range("I20").Value, but .Value is the
default, and I only tested it without .Value.

Then I read Jim's post, and tested with a 3D column chart, and it also
worked. It also ran without error in a radar chart, but had no effect on the
chart's appearance.

Judging from the error, I'd make sure that the chart objects are in fact
named "Chart 1", "Chart 2", etc.

- Jon
 
B

Breck

Because of your reply Jon about it working for you I poked around
some. This is what I discovered.

The original file was saves as a Excel 2007 macro enabled
workbook .xlsm.
When I opened the .xlsm file in Excel 2003 it went through a
conversion process so 2003 could read the file.
Because the macro worked for you I tried saving the workbook in 2003
as an 2003 workbook. .xls
I closed the program then reopened it.
I ran the macro and it worked perfectly..

So it appears to have something to do with opening a 2007
workbook .xlsm file in 2003 using the conversion program. I just have
to make sure that I save the workbook as a 2003 workbook when it is
going to opened with Excel 2003.

Thanks I would have never figured it out if you had not responded to
me request for help. Thank you very much.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update "centerheader" code running slowly need help speeding up 7
MinimumScale method of Axis Object failed 1
Changing a horizontal axis crosses value using VBA 5
Change minimumscale with the change event of a combobox 1
Graph - Runtime Error 1004 5
chart objects top 1
Ordering of bars in VBA charts created with ChartWizard and union() 2
Worksheet Change Sub problem 1

Top