Aug 20 2022 07:13 AM
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.
Aug 22 2022 09:26 AM - edited Aug 22 2022 09:28 AM
@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.
Aug 22 2022 10:55 AM
SolutionSee 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.
Aug 22 2022 11:12 AM - edited Aug 22 2022 11:15 AM
Thank you just what I wanted, cheers,
Aug 22 2022 11:47 AM
Aug 22 2022 12:23 PM
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.
Aug 22 2022 12:59 PM
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
Aug 22 2022 01:27 PM
@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.
Aug 23 2022 06:56 AM
@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.
Aug 23 2022 08:15 AM
See this version, with corrected code.
Aug 23 2022 08:32 AM - edited Aug 23 2022 09:57 AM
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.
Aug 23 2022 01:37 PM
Aug 23 2022 01:54 PM
I'd have to see the workbook...
Aug 24 2022 04:38 AM
Aug 22 2022 10:55 AM
SolutionSee 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.