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 Sub
Thank you just what I wanted, cheers,