Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Bruges, Belgium
    Thanked 2 Times in 2 Posts

    Disable Calculation While Spinning (2002 XP)

    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?

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Springfield, Ohio, USA
    Thanked 2 Times in 2 Posts

    Re: Disable Calculation While Spinning (2002 XP)

    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Disable Calculation While Spinning (2002 XP)

    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts