|
Changing a horizontal axis crosses value using VBA
I have just started trying to learn VBA since the 1st of the year. I
used macro recorder to record changes in the "Format Axis" "Axis Options" "Horizontal Axis Crosses" "Axis value" or the "CrossesAt" portion of a chart. Because I wanted to be able to enter a crosses at value into a cell I added the = Range(). The following code is working except there is a short wait while the code is executing. ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("I21") ActiveSheet.ChartObjects("Chart 2").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("J21") ActiveSheet.ChartObjects("Chart 3").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("K21") ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("L21") ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("M21") ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("N21") ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("O21") ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("P21") ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("Q21") Here is what I'm trying to do now. If the value in the chart is the same as in the cell I want to skip the code for that chart. So if the value in the cell is different need to run. I can't figure out how to compare the 2 values. This is what I have attempted without sucess. ActiveSheet.ChartObjects("Chart 1").Activate If Not ActiveChart.Axes(xlValue).CrossesAt.Value = Range("I20") Then ActiveChart.Axes(xlValue).CrossesAt.Value = Range("I20") End If I sure it something very simple and basic but I'm not sure what to do. Thanks in advance |
Changing a horizontal axis crosses value using VBA
Based on what I read in the help files:
ActiveSheet.ChartObjects("Chart 1").Activate If ActiveChart.Axes(xlValue).CrossesAt < Range("I20") Then ActiveChart.Axes(xlValue).CrossesAt = Range("I20") End If "Breck" wrote: I have just started trying to learn VBA since the 1st of the year. I used macro recorder to record changes in the "Format Axis" "Axis Options" "Horizontal Axis Crosses" "Axis value" or the "CrossesAt" portion of a chart. Because I wanted to be able to enter a crosses at value into a cell I added the = Range(). The following code is working except there is a short wait while the code is executing. ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("I21") ActiveSheet.ChartObjects("Chart 2").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("J21") ActiveSheet.ChartObjects("Chart 3").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("K21") ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("L21") ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("M21") ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("N21") ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("O21") ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("P21") ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("Q21") Here is what I'm trying to do now. If the value in the chart is the same as in the cell I want to skip the code for that chart. So if the value in the cell is different need to run. I can't figure out how to compare the 2 values. This is what I have attempted without sucess. ActiveSheet.ChartObjects("Chart 1").Activate If Not ActiveChart.Axes(xlValue).CrossesAt.Value = Range("I20") Then ActiveChart.Axes(xlValue).CrossesAt.Value = Range("I20") End If I sure it something very simple and basic but I'm not sure what to do. Thanks in advance |
Changing a horizontal axis crosses value using VBA
Each time you activate and select an object, you waste a little time. Also,
preventing the screen from updating during the process takes time and causes flickering of the display. Try this: Application.ScreenUpdating = False With ActiveSheet .ChartObjects("Chart 1").Chart.Axes(xlValue).CrossesAt = ..Range("I21").Value .ChartObjects("Chart 2").Chart.Axes(xlValue).CrossesAt = ..Range("J21").Value .ChartObjects("Chart 3").Chart.Axes(xlValue).CrossesAt = ..Range("K21").Value ' etc. End With Application.ScreenUpdating = True Notice that I've also qualified the Range with a dot, making it clear that the range is on the active sheet. This makes it easy also to work on a different sheet without activating it, by using this: With ActiveWorkbook.Worksheets("Sheet1") or even With Workbooks("Book1.xls").Worksheets("Sheet1") - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Breck" wrote in message ... I have just started trying to learn VBA since the 1st of the year. I used macro recorder to record changes in the "Format Axis" "Axis Options" "Horizontal Axis Crosses" "Axis value" or the "CrossesAt" portion of a chart. Because I wanted to be able to enter a crosses at value into a cell I added the = Range(). The following code is working except there is a short wait while the code is executing. ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("I21") ActiveSheet.ChartObjects("Chart 2").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("J21") ActiveSheet.ChartObjects("Chart 3").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("K21") ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("L21") ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("M21") ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("N21") ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("O21") ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("P21") ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("Q21") Here is what I'm trying to do now. If the value in the chart is the same as in the cell I want to skip the code for that chart. So if the value in the cell is different need to run. I can't figure out how to compare the 2 values. This is what I have attempted without sucess. ActiveSheet.ChartObjects("Chart 1").Activate If Not ActiveChart.Axes(xlValue).CrossesAt.Value = Range("I20") Then ActiveChart.Axes(xlValue).CrossesAt.Value = Range("I20") End If I sure it something very simple and basic but I'm not sure what to do. Thanks in advance |
Changing a horizontal axis crosses value using VBA
Thanks Jon.
It takes 3-4 seconds to complete the code. I have this code on a worksheet that activates when the sheet is accessed to make sure that the crosses at value is current by using Private Sub worksheet_activate() at the beginning of the code. The crosses at value is entered most of the time just once on a setup worksheet.The value from the setup sheet carries to the sheet that has the charts on it. I was thinking that a test to determine if a difference exists between the values currently in the chart and the amount in a cell before running the code would eliminate or reduce even further the 3-4 second delay. Is my thinking correct? On Feb 10, 8:13 am, "Jon Peltier" wrote: Each time you activate and select an object, you waste a little time. Also, preventing the screen from updating during the process takes time and causes flickering of the display. Try this: Application.ScreenUpdating = False With ActiveSheet .ChartObjects("Chart 1").Chart.Axes(xlValue).CrossesAt = .Range("I21").Value .ChartObjects("Chart 2").Chart.Axes(xlValue).CrossesAt = .Range("J21").Value .ChartObjects("Chart 3").Chart.Axes(xlValue).CrossesAt = .Range("K21").Value ' etc. End With Application.ScreenUpdating = True Notice that I've also qualified the Range with a dot, making it clear that the range is on the active sheet. This makes it easy also to work on a different sheet without activating it, by using this: With ActiveWorkbook.Worksheets("Sheet1") or even With Workbooks("Book1.xls").Worksheets("Sheet1") - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. -http://PeltierTech.com _______ "Breck" wrote in message ... I have just started trying to learn VBA since the 1st of the year. I used macro recorder to record changes in the "Format Axis" "Axis Options" "Horizontal Axis Crosses" "Axis value" or the "CrossesAt" portion of a chart. Because I wanted to be able to enter a crosses at value into a cell I added the = Range(). The following code is working except there is a short wait while the code is executing. ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("I21") ActiveSheet.ChartObjects("Chart 2").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("J21") ActiveSheet.ChartObjects("Chart 3").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("K21") ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("L21") ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("M21") ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("N21") ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("O21") ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("P21") ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("Q21") Here is what I'm trying to do now. If the value in the chart is the same as in the cell I want to skip the code for that chart. So if the value in the cell is different need to run. I can't figure out how to compare the 2 values. This is what I have attempted without sucess. ActiveSheet.ChartObjects("Chart 1").Activate If Not ActiveChart.Axes(xlValue).CrossesAt.Value = Range("I20") Then ActiveChart.Axes(xlValue).CrossesAt.Value = Range("I20") End If I sure it something very simple and basic but I'm not sure what to do. Thanks in advance |
Changing a horizontal axis crosses value using VBA
It takes 3-4 seconds to complete which code, the original or the one I
suggested that switches ScreenUpdating off and on and doesn't select charts? While screen updating and selection would have large effects on execution time, your thinking is correct, so you could take my code one step further: Application.ScreenUpdating = False With ActiveSheet If .ChartObjects("Chart 1").Chart.Axes(xlValue).CrossesAt < ..Range("I21").Value Then .ChartObjects("Chart 1").Chart.Axes(xlValue).CrossesAt = ..Range("I21").Value End If If .ChartObjects("Chart 2").Chart.Axes(xlValue).CrossesAt < ..Range("J21").Value Then .ChartObjects("Chart 2").Chart.Axes(xlValue).CrossesAt = ..Range("J21").Value End If ' etc. End With Application.ScreenUpdating = True - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Breck" wrote in message ... Thanks Jon. It takes 3-4 seconds to complete the code. I have this code on a worksheet that activates when the sheet is accessed to make sure that the crosses at value is current by using Private Sub worksheet_activate() at the beginning of the code. The crosses at value is entered most of the time just once on a setup worksheet.The value from the setup sheet carries to the sheet that has the charts on it. I was thinking that a test to determine if a difference exists between the values currently in the chart and the amount in a cell before running the code would eliminate or reduce even further the 3-4 second delay. Is my thinking correct? On Feb 10, 8:13 am, "Jon Peltier" wrote: Each time you activate and select an object, you waste a little time. Also, preventing the screen from updating during the process takes time and causes flickering of the display. Try this: Application.ScreenUpdating = False With ActiveSheet .ChartObjects("Chart 1").Chart.Axes(xlValue).CrossesAt = .Range("I21").Value .ChartObjects("Chart 2").Chart.Axes(xlValue).CrossesAt = .Range("J21").Value .ChartObjects("Chart 3").Chart.Axes(xlValue).CrossesAt = .Range("K21").Value ' etc. End With Application.ScreenUpdating = True Notice that I've also qualified the Range with a dot, making it clear that the range is on the active sheet. This makes it easy also to work on a different sheet without activating it, by using this: With ActiveWorkbook.Worksheets("Sheet1") or even With Workbooks("Book1.xls").Worksheets("Sheet1") - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. -http://PeltierTech.com _______ "Breck" wrote in message ... I have just started trying to learn VBA since the 1st of the year. I used macro recorder to record changes in the "Format Axis" "Axis Options" "Horizontal Axis Crosses" "Axis value" or the "CrossesAt" portion of a chart. Because I wanted to be able to enter a crosses at value into a cell I added the = Range(). The following code is working except there is a short wait while the code is executing. ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("I21") ActiveSheet.ChartObjects("Chart 2").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("J21") ActiveSheet.ChartObjects("Chart 3").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("K21") ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("L21") ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("M21") ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("N21") ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("O21") ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("P21") ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("Q21") Here is what I'm trying to do now. If the value in the chart is the same as in the cell I want to skip the code for that chart. So if the value in the cell is different need to run. I can't figure out how to compare the 2 values. This is what I have attempted without sucess. ActiveSheet.ChartObjects("Chart 1").Activate If Not ActiveChart.Axes(xlValue).CrossesAt.Value = Range("I20") Then ActiveChart.Axes(xlValue).CrossesAt.Value = Range("I20") End If I sure it something very simple and basic but I'm not sure what to do. Thanks in advance |
Changing a horizontal axis crosses value using VBA
Thank you very much. I liked learning about the qualified with a dot
to make sure it worked only on the active page plus the simplification of the code. I appreciate you time. On Feb 10, 11:24 am, "Jon Peltier" wrote: It takes 3-4 seconds to complete which code, the original or the one I suggested that switches ScreenUpdating off and on and doesn't select charts? While screen updating and selection would have large effects on execution time, your thinking is correct, so you could take my code one step further: Application.ScreenUpdating = False With ActiveSheet If .ChartObjects("Chart 1").Chart.Axes(xlValue).CrossesAt < .Range("I21").Value Then .ChartObjects("Chart 1").Chart.Axes(xlValue).CrossesAt = .Range("I21").Value End If If .ChartObjects("Chart 2").Chart.Axes(xlValue).CrossesAt < .Range("J21").Value Then .ChartObjects("Chart 2").Chart.Axes(xlValue).CrossesAt = .Range("J21").Value End If ' etc. End With Application.ScreenUpdating = True - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. -http://PeltierTech.com _______ "Breck" wrote in message ... Thanks Jon. It takes 3-4 seconds to complete the code. I have this code on a worksheet that activates when the sheet is accessed to make sure that the crosses at value is current by using Private Sub worksheet_activate() at the beginning of the code. The crosses at value is entered most of the time just once on a setup worksheet.The value from the setup sheet carries to the sheet that has the charts on it. I was thinking that a test to determine if a difference exists between the values currently in the chart and the amount in a cell before running the code would eliminate or reduce even further the 3-4 second delay. Is my thinking correct? On Feb 10, 8:13 am, "Jon Peltier" wrote: Each time you activate and select an object, you waste a little time. Also, preventing the screen from updating during the process takes time and causes flickering of the display. Try this: Application.ScreenUpdating = False With ActiveSheet .ChartObjects("Chart 1").Chart.Axes(xlValue).CrossesAt = .Range("I21").Value .ChartObjects("Chart 2").Chart.Axes(xlValue).CrossesAt = .Range("J21").Value .ChartObjects("Chart 3").Chart.Axes(xlValue).CrossesAt = .Range("K21").Value ' etc. End With Application.ScreenUpdating = True Notice that I've also qualified the Range with a dot, making it clear that the range is on the active sheet. This makes it easy also to work on a different sheet without activating it, by using this: With ActiveWorkbook.Worksheets("Sheet1") or even With Workbooks("Book1.xls").Worksheets("Sheet1") - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. -http://PeltierTech.com _______ "Breck" wrote in message ... I have just started trying to learn VBA since the 1st of the year. I used macro recorder to record changes in the "Format Axis" "Axis Options" "Horizontal Axis Crosses" "Axis value" or the "CrossesAt" portion of a chart. Because I wanted to be able to enter a crosses at value into a cell I added the = Range(). The following code is working except there is a short wait while the code is executing. ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("I21") ActiveSheet.ChartObjects("Chart 2").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("J21") ActiveSheet.ChartObjects("Chart 3").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("K21") ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("L21") ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("M21") ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("N21") ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("O21") ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("P21") ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.Axes(xlValue).CrossesAt = Range("Q21") Here is what I'm trying to do now. If the value in the chart is the same as in the cell I want to skip the code for that chart. So if the value in the cell is different need to run. I can't figure out how to compare the 2 values. This is what I have attempted without sucess. ActiveSheet.ChartObjects("Chart 1").Activate If Not ActiveChart.Axes(xlValue).CrossesAt.Value = Range("I20") Then ActiveChart.Axes(xlValue).CrossesAt.Value = Range("I20") End If I sure it something very simple and basic but I'm not sure what to do. Thanks in advance |
| All times are GMT +1. The time now is 12:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com