Spinbutton won't stop counting

[Izran]

In this application I have a cell value (AUX_KW_ADJUST) that gets
adjusted by a combination of spin buttons (one for the one, ten and
hundred place). Another cell's value (FUTURE_KW_AVAILIBLE) is
evaluated to see if AUX_KW_ADJUST is gonna exceed exceed its max limit;
if it is going to exceed the spin button is disabled. The problem is
while it does succeed in disabling the spin button, the cell keep
counting as though the spin button is still being pressed (it visually
apppears to be active). So I guess I'm looking for an answer to one of
two things here - How to force the spinbutton to be
"deselected"/"deactivated" or a way to have it deselect itself (i.e.
make the counting stop). Here is the abbreviated code with just the
spinbutton aspects included:


*****WORKSHEET******

Private Sub AUX_KW_ADJUST_1_SPINBUTTON_SpinUp()

If Range("FUTURE_AVAILIBLE_KW") >= 1 Then

Range("AUX_KW_ADJUST").Value = Range("AUX_KW_ADJUST").Value + 1

End If

Call Buttons_Enabled
Call Buttons_Disabled

End Sub


Private Sub AUX_KW_ADJUST_10_SPINBUTTON_SpinUp()

If Range("FUTURE_AVAILIBLE_KW") >= 10 Then

Range("AUX_KW_ADJUST").Value = Range("AUX_KW_ADJUST").Value +
10

End If

Call Buttons_Enabled
Call Buttons_Disabled

End Sub


Private Sub AUX_KW_ADJUST_100_SPINBUTTON_SpinUp()

If Range("FUTURE_AVAILIBLE_KW") >= 100 Then

Range("AUX_KW_ADJUST").Value = Range("AUX_KW_ADJUST").Value +
100

End If

AUX_KW_ADJUST_100_SPINBUTTON.Locked = True
AUX_KW_ADJUST_100_SPINBUTTON.Locked = False


Call Buttons_Enabled
Call Buttons_Disabled

End Sub






******MODULE**********

Sub Buttons_Enabled()

If Range("FUTURE_AVAILIBLE_KW") >= 1 Then
Worksheets(1).AUX_KW_ADJUST_1_SPINBUTTON.Enabled = True _
Else Worksheets(1).AUX_KW_ADJUST_1_SPINBUTTON.Enabled = False

If Range("FUTURE_AVAILIBLE_KW") >= 10 Then
Worksheets(1).AUX_KW_ADJUST_10_SPINBUTTON.Enabled = True _
Else Worksheets(1).AUX_KW_ADJUST_10_SPINBUTTON.Enabled = False

If Range("FUTURE_AVAILIBLE_KW") >= 100 Then
Worksheets(1).AUX_KW_ADJUST_100_SPINBUTTON.Enabled = True _
Else Worksheets(1).AUX_KW_ADJUST_100_SPINBUTTON.Enabled = False

End Sub


Sub Buttons_DIsabled()

'disables several other buttons - limits the user to one action

End Sub

 

[Tom Ogilvy]

Do you have change event code that could be causing this to happen. You
might adjust your code to do

Private Sub AUX_KW_ADJUST_1_SPINBUTTON_SpinUp()

If Range("FUTURE_AVAILIBLE_KW") >= 1 Then
Application.EnableEvents = False
Range("AUX_KW_ADJUST").Value = Range("AUX_KW_ADJUST").Value + 1
Application.EnableEvents = True
End If

Call Buttons_Enabled
Call Buttons_Disabled
end Sub

do this in all the places where you change the AUX_KW_ADJUST value.

 

[Izran]

Tom,

I don't have any change event code. Tried the suggested code change
and it still is locking up. It's lacting like the spinup is stuck. It
exits the spinup() subroutine adn spinup is actually still pressed on
the spinbutton causing it to jump back in. I need to somehow force the
spinbutton to deselect itself.

Mike

 

[Tom Ogilvy]

Somewhere, I think you have recursive calls in your code (procedure A calls
procedure B, then procedure B calls procedure A).

To test in the events, in each event, put in code at the top like:

debug.print "in event such and such"

then press the spinner and get it to stop.

Then go into the VBE and look in the immediate window. (ctrl+G if it isn't
visible) and see if you see a pattern of calls.

 

[Izran]

Tom,

I triple checked the code and there are no recursive calls. I even
stepped through the code and it steps through fine. I'm starting to
think when the Spinner gets disabled, it locks into its current state.
Because it's stepping out of the program and then stepping right back
in like its been pushed again.

Regards,
Mike

 

[Tom Ogilvy]

No, it doesn't operate that way.

 

[Izran]

Hmmmmm....

Okay. I've reduced the code to just what I want single spinner to do
and it still locks up. It works perfectly fine without the last if
statement (which was taken from teh buttons_enabled subroutine), but
when I place it in there, the program continues to enter and exit the
spinup sub as before. For some reason, the spin button is stuck in its
pressed mode.

Private Sub AUX_KW_ADJUST_100_SPINBUTTON_SpinUp()

If Range("FUTURE_AVAILIBLE_KW") >= 100 Then

Application.EnableEvents = False
Range("AUX_KW_ADJUST").Value = Range("AUX_KW_ADJUST").Value +
100
Application.EnableEvents = True

End If

' Call Buttons_Enabled
Call Buttons_Disabled

If Range("FUTURE_AVAILIBLE_KW") >= 100 Then
Worksheets(1).AUX_KW_ADJUST_100_SPINBUTTON.Enabled = True _
Else Worksheets(1).AUX_KW_ADJUST_100_SPINBUTTON.Enabled = False



End Sub