Disable calculation while spinning

[rvExcelTip]

A spreadsheet has a Spin button that is linked to a cell. I want th
user to have the possibility of either to spin or to enter a valu
directly in the linked cell. So far, so good. Except that th
spreadsheet recalculates every time whilst spinning. As the amount o
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 t
test/toggle it at the change/spinup or down event. But I hit upon
logical conundrum in that the order of events is first Change the
Spinup/Down and apparently there is no (?) way to detect the end of th
Spinning.

I then tried to add the KeyDown/KeyUp event. The idea was that the use
could decide for him/herself to quick spin or not (by pushing the Shif
key). Therefore I would disable calculation in KeyDown and t
re-enable it in KeyUp and then Call by code for the Change event. But
got into all sorts of trouble:

The event is reported as follows:
PRIVATE SUB[/B] -OBJECT-_*KEYDOWN(BYVAL* -KEYCODE- *A
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 wa
unkown. Why do I get an error for fmShiftMask? Did I forget to add
reference?

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

Can somebody get me out of this trap? :confused

 

[Bob Phillips]

Sorry, I got a bit lost in all of this (probably me), but here is some code
to show you how to detect the shift key if that helps

Private Declare Function GetKeyState Lib "user32" (ByVal fnKey As Long) As
Integer

Const vkShift As Integer = &H10

Private Sub SpinButton1_SpinUp()

If GetKeyState(vkShift) < 0 Then
Label1.Caption = CLng(Label1.Caption) + 10
Else
Label1.Caption = CLng(Label1.Caption) + 1
End If

End Sub

Private Sub SpinButton1_SpinDown()

If GetKeyState(vkShift) < 0 Then
Label1.Caption = CLng(Label1.Caption) - 10
Else
Label1.Caption = CLng(Label1.Caption) - 1
End If

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)