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.
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.
How do I this for 100 row so I know which active cell I am on to run the macro.
or suggest a good book which will teach me how to do what I required.
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 SubHansVogelaar 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.
HansVogelaar Wow Another Thank you. Never would I have worked that out having only just jumped into this part of coding. Will help a lot in learning how to do this. Like how to set up a loop and many other things. Had been trying with two if statement but nothing, apart from breaking it all.
Will you have 100 spinners? If so, it would be helpful if the name of the spinners were consistent - for example Spinner 1 in row 2, Spinner 2 in row 3, etc., without exception.