Solved: Run macro when spinner value changed

[tammyl]

Hi,
I'm trying to run a macro when a spinner value is changed.
I have a spinner button control 'spinner 3' linked to cell '$A$2'.
Currently this cell has a year value. Eg: 2008
In cell A5 I have formula =Date(TheYear,1,1) 'Equals 2008/1/1
'TheYear' is the defined name of the spinner cell $A$2.
When the value changes in cell A2 using the spinner button, I wish to run this macro. It does a fill date series.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Range
For Each i In Target
If i.Address = "$A$2" Then
Range("A5:A375").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _
xlDay, Step:=1, Trend:=False
Range("B6").Select
End If
End Sub
The macro is not called when I use the spinner button to change the A2 cell value, I think the macro needs me to manually change it. Is there a way I can link the macro to run.
Cheers tammyl

[GTO]

Hi Tammy,

In answer to your question, I believe you are referring to a spinner button from the Forms toolbar. While I don't really use these, I just tested, and as the control is "Linked" to the cell, the cell's value changing (via spin button use) doesn't fire the worksheet change event.

Beyond that, a couple of issues w/the loop.

You are missing the Next at the end, which you would have found if it ran.

For Each i In Target
If i.Address = "$A$2" Then
Range("A5:A375").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _
xlDay, Step:=1, Trend:=False
Range("B6").Select
End If
Next

More inportantly, I cannot see a reason for the loop, leastwise not the way it appears you were trying to key-in on whether A2 had changed.

Anyways, might I suggest a rather easy fix? As A5 is updated everytime the scroll bar is used anyways, why not just use the formula =A5 + 1 in A6, and drag down?

Now if there is a reason you'd rather not have formulas, then you could use...

Private Sub Worksheet_Calculate()
Range("A5:A375").DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _
xlDay, Step:=1, Trend:=False

End Sub

...though, I would pick using a formula if possible, especially when comparing to something like the calculate or worksheet change events, since they run so often.

Hope this helps,

Mark

[tammyl]

Thanks Mark.
I used the formula instead of the macro as you suggested and it works great.

PS. Not sure how to mark this thread 'Solved'.
Cheers tammyl

[GTO]

Hey tammyl,

Glad that was an easy fix. To mark the thread solved, look at the green bar at the top of your first post. Under the 'Thread Tools' button there is a Mark as Solved (or similar verbiage) button. :-)

Have a terrific day,

Mark