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?