Excel remains in run mode after spin button change

[Bastiaan Meijerink]

Hello everyone,

If I start a blank Excel 2010 document, place an ActiveX spinbutton and consequently trigger it, the spinbutton itself becomes selected and Excel remains in VBA run mode (buttons in menu can not be pressed).

Of course, this behavior does not occur with a Form Control spinbutton.

I am very curious if there is a simple solution to this problem? I must be overlooking something.

Thanks and kind regards,

Bas

 

[VoG]

Did you click on Design Mode to take Excel out of Design Mode?

 

[Bastiaan Meijerink]

Did you click on Design Mode to take Excel out of Design Mode?

VoG,

Yes, even the use of standard 'endings' like:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

do not seem to do the trick.

 

[Bastiaan Meijerink]

It seems that the spinbutton (sometimes) keeps focus after it is pressed. A workaround that worked in my example is to also implement a GotFocus event for the spinbutton in VBE. In this case:

Sub SpinButton1_GotFocus()
[A1].Activate
End Sub

The focus now redirects to cell A1 every time the spin button is pressed.