*Resolved* Excel & "On SelectionChange" question

[TheRobster]

I have a query I am hoping you can help me with.

I have an Excel chart and I want the user to be able to change to range of the x-axis to any number between 2 and 100. They do this simply by entering a number in a cell and the chart x-axis should automatically update so that the Max. Number for the x-axis equals the user input value.

For the sake of argument suppose that the cell that the user needs to enter the number in is cell A1.

I already have the code that changes the x-axis, which is as follows:

With ActiveSheet
        .ChartObjects("Chart 1").Chart.Axes(xlCategory).MaximumScale = Range("A1")
End With

The problem is I don't know how to get the spreadsheet to execute this piece of code. I thought that the SelectionChange event might be the one I need but I can't get it to work.

Also, ideally I only want the spreadsheet to do anything if cell A1 only is changed i.e. it doesn't need to update the chart x-axis if some other cell is changed.

So ideally I need code that says:

If Cell A1 is changed then make the x-axis of Chart 1 equal the new value

Thanks in advance
-Rob

[RobDog888]

Either should do it. Your choice .

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        'Update chart
    End If
End Sub
 
'Or
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        'Update chart
    End If
End Sub

[StevenHickerson]

Note for what RobDog said. If I remember correctly the _Change sub will only fire after the user exits the cell that is being changed.

To elaborate... say you type 50 in the cel and leave the cursor sitting there.. it wont fire the event, but if you move the cursor to another cell then the event fires if it was changed from its previous value.

[RobDog888]

Correct and good description of the event.

You could also add a menu or toolbar button to do your chart refresh if you want to also.

[TheRobster]

Cheers for the replies guys.

You could also add a menu or toolbar button to do your chart refresh if you want to also.

No, the methods you outlined earlier will be fine. Before I posted my query on here I was actually thinking about using a button labelled "Update" or something similar and binding this to a macro which would update the chart x-axis but your solution is more elegant.

-Rob

[RobDog888]

As always we are glad to have helped.

Btw, did you see that we have a new way to do a simple "[RESOLVED]"? Its from the Thread Tools menu > Mark Thread Resolved.