Changing a horizontal axis crosses value using VBA

[Breck]

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

 

[JLGWhiz]

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

 

[Jon Peltier]

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

 

[Breck]

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?

 

[Jon Peltier]

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

 

[Breck]

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.