SOLVED

Disabling and re enabling Spinners controls in 2010 and setting the count back to zero.

Occasional Contributor

Hi all,

I am novice with anything to do with excel, most self taught.

Right my question is, can a a non Active X control spinner be disabled and then reactivated and the count reset to zero.
I am using the way that is shown on a few Youtube videos.

 

Have found a way to enable disable my count from the spinner, however if I click the spinner when disabled it will still count up or down. plus when I re enable the line, it returns the count that the spinner was last at, not zero as I would like.

 

plus a cannot see or find a way to set the spinner back to zero.

 

Its possible that it can't be done with from control, and only use Active X controls and VBA. 

 

if the later I going to have to learn VBA too.

13 Replies

@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. 

best response confirmed by Stereone (Occasional Contributor)
Solution

@Stereone 

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.

Thank you just what I wanted, cheers, 

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.

@Stereone 

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.

@Stereone 

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

@Hans Vogelaar 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.

@Hans Vogelaar 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.

@Stereone 

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.
Turns out that your first code was right, as when I put it back to that in my new version all started to work, as the first spinner starts at number is 2.