Change graph axis with VBA based on cell value that is a formula

[rub]

Hi,

I am using VBA to automatically update the Y-axis minimum and maximum based on a value in my excel sheet. However, this value is determined by a formula (lowest value in the dataset - 10%).
If I get rid of the formula in this cell and just have a value in there, it works fine using this code:

If Target.Address = "$F$2" Then
ActiveSheet.ChartObjects("Grafiek 7").Chart.Axes(xlValue).MinimumScale = Range("F2").Value
End If

If Target.Address = "$G$2" Then
ActiveSheet.ChartObjects("Grafiek 7").Chart.Axes(xlValue).MaximumScale = Range("G2").Value
End If

Where F2 is the value that has the minimum for the Y axis and G2 the maximum.

The actual minimum and maximum are:
Minimum: H2-(H2*0,1)
Maximum: I2+(I2*0,1)


Can someone help me to get VBA to recognise the value from a formula and use this to set the minimum and maximum values of the Y axis?

Thanks!

 

[NdNoviceHlp]

Hi again rub. You should be able to set named ranges for your F2 and G2 values and then reference the named range in your code. HTH. Dave

 

[rub]

Hi again rub. You should be able to set named ranges for your F2 and G2 values and then reference the named range in your code. HTH. Dave

Hi Dave,

Thanks for the quick reply.

What I did:

If Target.Address = "$F$2" Then
        ActiveSheet.ChartObjects("Grafiek 7").Chart.Axes(xlValue).MinimumScale = Range("Ymin1").Value
End If

If Target.Address = "$G$2" Then
        ActiveSheet.ChartObjects("Grafiek 7").Chart.Axes(xlValue).MaximumScale = Range("Ymax1").Value
End If

I set the 2 cells as range Ymin1 and Ymax1, copied the formula into these cells and it works almost. The only thing is when I change the input of the graph and Ymin1 and Ymax1 change, I have to 'confirm' the formula in this cell (i.e. select the cell, press F2, press enter).

Do you know why this happens?

Thanks!

 

[NdNoviceHlp]

Trial this...

 ActiveSheet.Range("Ymin1").Formula

Dave

 

[rub]

Trial this...

 ActiveSheet.Range("Ymin1").Formula

Dave

No luck unfortunately when doing this:

If Target.Address = "$F$2" Then
        ActiveSheet.ChartObjects("Grafiek 7").Chart.Axes(xlValue).MinimumScale = Range("Ymin1").Formula
End If

If Target.Address = "$G$2" Then
        ActiveSheet.ChartObjects("Grafiek 7").Chart.Axes(xlValue).MaximumScale = Range("Ymax1").Formula

It doesn't update the graphs.

Any other ideas?

 

[NdNoviceHlp]

rub are you using a sheet change event to control this? You need to create an event in order for it to work. For example...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$2" Then
With ActiveSheet.ChartObjects("Grafiek 7").Chart.Axes(xlValue, xlPrimary)
.MinimumScale = ActiveSheet.Range("Ymin1").Value
End With
End If
End Sub

Sub ChangeMin()
ActiveSheet.Range("F2") = 2
End Sub

Run the ChangeMin sub to adjust the value of "F2" which will generate your sheet change event which will update your chart. I'm not certain that a named range is needed but I did trial the above code and it did work to adjust the minimum scale of my test chart. Dave

 

[rub]

rub are you using a sheet change event to control this? You need to create an event in order for it to work. For example...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$2" Then
With ActiveSheet.ChartObjects("Grafiek 7").Chart.Axes(xlValue, xlPrimary)
.MinimumScale = ActiveSheet.Range("Ymin1").Value
End With
End If
End Sub

Sub ChangeMin()
ActiveSheet.Range("F2") = 2
End Sub

Run the ChangeMin sub to adjust the value of "F2" which will generate your sheet change event which will update your chart. I'm not certain that a named range is needed but I did trial the above code and it did work to adjust the minimum scale of my test chart. Dave

Hi Dave, yes it is a worksheet change event. I tried your code and it works once, but then when F2 is changing it doesn't update anymore.
I've created a simple file to test this, can you see if this should be correct?

Thanks Rub

 

[NdNoviceHlp]

Argghh... I hate worksheet change events. Anyways, after a renewed look over this thread, you have formulas in "F2" and "G2" which you want to use to dynamically update min and max scales of your chart. These formulas are based on "H2" and "I2" value changes (which I'm hoping don't have formula values). The named ranges don't seem necessary although I thought they should work. So...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$2" Or Target.Address = "$I$2" Then
Call ChangeScale
End If
End Sub

Sub ChangeScale()
With ActiveSheet.ChartObjects("Grafiek 7").Chart.Axes(xlValue, xlPrimary)
.MinimumScale = ActiveSheet.Range("f2").Value
.MaximumScale = ActiveSheet.Range("G2").Value
End With
End Sub

You could also get rid of the worksheet change event (yeah!) with worksheet calculate event code but it would be firing all the time and probably not be ideal....

Private Sub Worksheet_Calculate()
Call ChangeScale
End Sub

To use the sheet change event, you will probably have to refer to whatever cell is originally changed when data is entered which eventually causes the scale values to change. HTH. Dave

 

[rub]

Argghh... I hate worksheet change events. Anyways, after a renewed look over this thread, you have formulas in "F2" and "G2" which you want to use to dynamically update min and max scales of your chart. These formulas are based on "H2" and "I2" value changes (which I'm hoping don't have formula values). The named ranges don't seem necessary although I thought they should work. So...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$2" Or Target.Address = "$I$2" Then
Call ChangeScale
End If
End Sub

Sub ChangeScale()
With ActiveSheet.ChartObjects("Grafiek 7").Chart.Axes(xlValue, xlPrimary)
.MinimumScale = ActiveSheet.Range("f2").Value
.MaximumScale = ActiveSheet.Range("G2").Value
End With
End Sub

You could also get rid of the worksheet change event (yeah!) with worksheet calculate event code but it would be firing all the time and probably not be ideal....

Private Sub Worksheet_Calculate()
Call ChangeScale
End Sub

To use the sheet change event, you will probably have to refer to whatever cell is originally changed when data is entered which eventually causes the scale values to change. HTH. Dave

Yes, this is it! Thanks! My worksheet was a bit slow, so I had to tidy it up to make it work well, but it's doing what I want now. Thanks a lot Dave!

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Dave