Disable Calculation While Spinning (2002 XP)

[rvWoody]

A spreadsheet has a Spin button that is linked to a cell. I want the user to have the possibility of either to spin or to enter a value directly in the linked cell. So far, so good. Except that the spreadsheet recalculates every time whilst spinning. As the amount of calculation is quite important, the spinning is seriously slowed down, and the spreadsheet user's blood pressure is accordingly up.

I tried implementing a boolean on the spreadsheet module level and to test/toggle it at the change/spinup or down event. But I hit upon a logical conundrum in that the order of events is first Change then Spinup/Down and apparently there is no (?) way to detect the end of the Spinnng.

I then tried to add the KeyDown/KeyUp event. The idea was to disable calculation in KeyDown and to re-enable it in KeyUp and then Call by code for the Change event. But I got into all sorts of trouble:

The event is reported as follows:
Private Sub object_KeyDown(ByVal KeyCode as MSFORMS.ReturnInteger, ByVal Shift As fmShiftState)

When testing for the Shift value. e.g. If Shift = fmShiftMask Then .... I got a compile error, telling that the fmShiftMask Constant was unkown

Alternatively when testing for its numerical value, which is presumed to be 1 for the SHIFT key, I discovered that the variable was always zero !!

Can somebody get me out of this trap?

[SammyB]

Short of using a timer, the best that I could do was use the change event to disable calculations and the lostfocus event to enable calculations:
<pre>Option Explicit
'
Private Sub SpinButton1_Change()
Application.Calculation = xlCalculationManual
End Sub
'
Private Sub SpinButton1_LostFocus()
Application.Calculation = xlCalculationAutomatic
End Sub</pre>

But, this means that the user would have to click on the worksheet to do the calculation. It's annoying that the keydown/up do not work. HTH --Sam

[sdckapr]

Instead of using the spinner from the FORMS toolbar, use the one from the Control toolbox. This has events for spindown, spinup, keyup, keydown, etc which you can react to.

The Forms controls only have 1 event that you can trigger.

Steve