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.
Stereone find attached a simple demo of what I am trying to do, first sheet shows the count with spinners, second sheet has the Enabled N/A status.
What I am trying to do is when the input line show N/A there no input and the spinner should not go up or down which it does, would like to set it to zero when showing N/A.
Here hoping something to look might help understand the problem.
Cheers.
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.
- bet you guess what I am about ask now!
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.
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.
Thank you just what I wanted, cheers,