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

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
>

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" <(E-Mail Removed)> wrote in message
news:86df4ad5-4f42-482a-a787-(E-Mail Removed)...
>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

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" <jonxlmv...@SPAMpeltiertech.com>
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" <betuttl...@gmail.com> wrote in message
>
> news:86df4ad5-4f42-482a-a787-(E-Mail Removed)...
>
> >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

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" <(E-Mail Removed)> wrote in message
news:5c2f4167-cfd6-4f3c-ac30-(E-Mail Removed)...
> 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" <jonxlmv...@SPAMpeltiertech.com>
> 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" <betuttl...@gmail.com> wrote in message
>>
>> news:86df4ad5-4f42-482a-a787-(E-Mail Removed)...
>>
>> >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
>

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" <jonxlmv...@SPAMpeltiertech.com>
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" <betuttl...@gmail.com> wrote in message
>
> news:5c2f4167-cfd6-4f3c-ac30-(E-Mail Removed)...
>
> > 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" <jonxlmv...@SPAMpeltiertech.com>
> > 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" <betuttl...@gmail.com> wrote in message
>
> >>news:86df4ad5-4f42-482a-a787-(E-Mail Removed)...
>
> >> >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