Forum Discussion
Disabling and re enabling Spinners controls in 2010 and setting the count back to zero.
See the attached version. It is now a macro-enabled workbook, so you'll have to allow macros when you open it.
To inspect the code, right-click the sheet tab and select 'View Code' from the context menu.
With spinners in rows 2 to 101, linked to E2:E101, the code would look like this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim r As Long
If Not Intersect(Range("E2:E101"), Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each rng In Intersect(Range("E2:E101"), Target)
r = rng.Row
With Me.Shapes("Spinner " & r + 1).ControlFormat
If rng.Value = "Enabled" Then
.Enabled = True
Else
.Value = 0
.Enabled = False
End If
End With
Next rng
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
HansVogelaar Inserted the code into the sheet, It moaned about the spinner numbers first time I ran it, then after checking the spinner numbers to make sure they where in sequence, ran it again ,no complaints, however what it was doing with one if statement no long happens, when N/A is showing it should show zreo and not be able count up/down, this no longer does this.
have also rem parts of the code to say what I think is going on.
have upload the the code so you can have a look.
Thank you if you help again.
what happen before was this
recap:-
1. N/A showing the pale blue background colour and zero stayed at zero.
2. Enable showing and count is zero, red background colour.
3. Enable showing and count >0 , pale green background colour.
cheers.
See this version, with corrected code.
AAAh! the "spinner " & r -1 instead of + 1.
so r=4, with the above code r-1 would be 3 but the incorrect code made it 5.
I see, I am on row 4 but my spinner is number 3.
Thanks , had to write out the above to explain to myself, plus anyone looking in.- Just checking, When I started a new file, I copied the code from above and changed the + to -, and setup the other parts for colour formatting and added a spin button, did I miss a step as code does not stop the up count when on N/A like does in the above file bbtest1-2.xlsm.
Was there another step I should have done.
Cheers.