|
Excel 2003 code to use instead of ChartObjects
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 |
Excel 2003 code to use instead of ChartObjects
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 |
Excel 2003 code to use instead of ChartObjects
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jim Cone" wrote in message ... 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 |
Excel 2003 code to use instead of ChartObjects
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. On Mar 2, 1:14 pm, "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. -http://PeltierTech.com _______ "Jim Cone" wrote in message ... 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 |
Excel 2003 code to use instead of ChartObjects
Apparently compatibility is relative.
- Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Breck" wrote in message ... 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. On Mar 2, 1:14 pm, "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. -http://PeltierTech.com _______ "Jim Cone" wrote in message ... 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 |
| All times are GMT +1. The time now is 08:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com