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

Topic

New Reply

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

Private Sub SpinButton1_SpinUp()
ActiveWorkbook.Close
End Sub

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.

Reply | Quote

Replies

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

Steve

Reply | Quote

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).

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Thanks for checking, Steve.

Ian.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Ian.

Reply | Quote