worksheet change events don't execute until 2nd time cell is visited 1

[electricpete]

I want to create a chart that will have many user controls. I would like to have the chart embedded in a spreadsheet with many parameters accross the top of the sheet that control the chart's appearnce (for example xmin, xmax, ymin, ymax... many more to follow). When the user changes a cell, the worksheet change event sub will look at which cell was changed and decide what to do.

I have programmed this in excel file linked here:

http://home.comcast.net/~electricpete/eng-tips/ChartScaleChange.xls

The code is shown below.

The problem is: The sub doesn't seem to do anything until the second time I enter the cell. For example if I change xmax, nothing happens. If I select another cell and then select xmax, the chart updates. I also put msgbox("hello") into the code for changing xmin. It doesn't execute until the second time that cell is visited.

Why doesn't the code execute immediately when the data is changed ?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target(1, 1) = Range("xmin") Then
    With ActiveSheet.ChartObjects(1).Chart.Axes(xlCategory)
         .MinimumScale = Target
    End With
    MsgBox ("hello")
 End If
If Target(1, 1) = Range("xmax") Then
    With ActiveSheet.ChartObjects(1).Chart.Axes(xlCategory)
         .MaximumScale = Target
    End With
    
End If
If Target(1, 1) = Range("ymin") Then
    With ActiveSheet.ChartObjects(1).Chart.Axes(xlValue)
             .MinimumScale = Target
    End With
End If
If Target(1, 1) = Range("ymax") Then
    With ActiveSheet.ChartObjects(1).Chart.Axes(xlValue)
         .MaximumScale = Target
    End With
End If
' Stop
End Sub

Thanks in advance for any suggestions.

 

[ck1999]

Why not use worksheet_change instead of worksheet_selectionchange

ck1999

 

[ck1999]

You an also clean up your code some by using select

Select Case Target(1, 1)
    Case Range("xmin"):  ActiveSheet.ChartObjects(1).Chart.Axes(xlCategory).MinimumScale = Target
    Case Range("xmax"): ActiveSheet.ChartObjects(1).Chart.Axes(xlCategory).MaximumScale = Target
    Case Range("ymin"): ActiveSheet.ChartObjects(1).Chart.Axes(xlValue).MinimumScale = Target
    Case Range("ymax"): ActiveSheet.ChartObjects(1).Chart.Axes(xlValue).MaximumScale = Target
End Select

End Sub

ck1999

The reason selection change did not work is that target is the current cell (the one just moved to) not the last cell (the one you left)

 

[electricpete]

Fantastic, that was exactly my problem. I was expecting it to act like a worksheet change event...didn't even realize I had selected worksheet selection change event instead. I'm embarassed to say I have spent more than an hour trying to figure that out.

And yes, the case structure will be much neater since I intend to place a lot of control values in cells.

I have made the correction and re-uploaded the corrected file to the same location:

http://home.comcast.net/~electricpete/eng-tips/ChartScaleChange.xls

I notice the chart updates as expected, including entry into the ymax cell. But if I change ymax with the spinner control, the cell changes, but the chart is oblivious. Does that make sense?

 

[ck1999]

Add this

Private Sub SpinButton1_Change()
Worksheet_Change Range("m2")
End Sub

to your sheet1 vba

This works on my copy

ck1999

 

[electricpete]

Works like a charm.

I am impressed, and indebted.

Thanks!