Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SpinButton causes crash (Windows 2000 & Me/Excel 2000)

    I have a very simple Excel workbook (much cut down from the real one!) with one sheet on which there is a single SpinButton control. The SpinUp event code for the button is
    <pre>Private Sub SpinButton1_SpinUp()
    ActiveWorkbook.Close
    End Sub</pre>

    When I click the Up arrow on the SpinButton, Excel crashes with the helpful message "Excel has caused an error in EXCEL.EXE. Excel will now close". The same happens on two computers, one running Me and one W2000.

    If I replace the SpinButton with a CommandButton with the same code in its Click event, all is well. A simple workaround is to replace the spinbutton with two command buttons, but I wondered if anyone had any idea why the problem occurs.

    Thanks
    Ian.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SpinButton causes crash (Windows 2000 & Me/Excel 2000)

    FYI,
    I was NOT able to reproduce:
    These symptoms do NOT occur with XL97 in Win95 or WinXP.

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SpinButton causes crash (Windows 2000 & Me/Excel 2000)

    Please forgive me for saying so, but using a spinbutton to close a workbook seems rather unusual to me. The purpose of a spinbutton is to increase/decrease a value; using it to close a workbook is probably not something Microsoft envisioned when designing it. It is not in line with a consistent Windows interface.

    For the record: if I click the down arrow, then the up arrow, the workbook closes normally; if I click the up arrow without first clicking the down arrow, Excel crashes after closing the workbook (Excel 2002 SP-2).

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SpinButton causes crash (Windows 2000 & Me/Excel 2000)

    Yes, of course a spinbutton isn't usually the most logical place to close a workbook. I was half expecting such a comment!

    There is a reason, however! I am using a workbook to keep records of scores of a computer game. Spin buttons count wins and losses and after a certain number of games it puts up a MsgBox asking 'do you want to quit' . If you click 'Yes" the workbook is closed. Except that Excel crashes instead which is a nuisance if there are other workbooks open at the time.

    After steadily deleting controls and code, I discovered the source of the problem and wondered if it was known. I simplifed the code to its bare essential so there was nothing else that could be causing the problem.

    Thanks for the comments!

    Ian.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SpinButton causes crash (Windows 2000 & Me/Excel 2000)

    I tried it again after reading your note about clicking the down arrow first.

    I opened the file and clicked ONLY on the up arrow (I never clicked on the down arrow at all) and I still could NOT duplicate the problem in XL97.

    The bug must have occured starting in XL2000.

    Steve

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SpinButton causes crash (Windows 2000 & Me/Excel 2000)

    Thanks for checking, Steve.

    Ian.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SpinButton causes crash (Windows 2000 & Me/Excel 2000)

    It also crashes my XL2K SP3 if I click the up arrow first, but closes the workbook normally if I click the down arrow then the up arrow. I agree that this must be a bug in XL.
    Legare Coleman

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SpinButton causes crash (Windows 2000 & Me/Excel 2000)

    This works for me:

    In the class module of the sheet:

    Private Sub SpinButton1_SpinUp()
    Application.OnTime Now, "closeIt"
    End Sub

    In a normal module:

    Sub closeIt()
    ThisWorkbook.Close False
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SpinButton causes crash (Windows 2000 & Me/Excel 2000)

    Thanks, Jan. That works for me too! Gee you're smart!

    I'd already tried calling a sub in a normal module from the event sub in the class module - which didn't work. Presumably the 'OnTime' call gets the control completely away from the SpinButton module and allows the Close to proceed properly.

    Thanks again.
    Ian.

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SpinButton causes crash (Windows 2000 & Me/Excel 2000)

    Yes, the Ontime method enables VBA to first end all (possibly nested) tasks and then starts a macro in a normal module. I use this appraoch regularly when I expect trouble.

    This stems back from the early Excel 5 days, where the Auto_open macro could not be too long.

    It still applies sometimes, e.g. when one tries to create commandbars from witin the Thisworkbook module (if I remember correctly).

    There is another good reason to keep as much code in standard modules as possible:

    VBA does a bad job in cleaning after itself, bloating your workbook with leftovers from editing. This can cause irratic behaviour and crashes. To avoid this, one needs to cleanup regularly, which is not possible for modules behind sheets and for the Thisworkbook module. The cleaning process involves exporting the module and deleting it and then importing the exported file. Deleting code modules behind sheets is NOT possible and hence that codemodule cannot be cleaned.

    This cleaning process is automated by Rob Bovey's code cleaner, to be downloaded from the Excel MVP page below.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SpinButton causes crash (Windows 2000 & Me/Excel 2000)

    Thanks again! I tried the Export/Delete/Import on a smallish file and got a 20% reduction in size.

    Ian.

Posting Permissions

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