PDA

View Full Version : Exel Crashes with Form Control



Torchwood
03-10-2014, 06:00 AM
Here's an odd behaviour that I've no idea how to fix ...

(Using Excel 2010 32-bit on Windows 7)

Setup:
+ Create two Excel files on desktop: Master.xlsm and Slave.xlsm

+ Master.xlsm contains two tabs: Sheet1 and Sheet2. Add a simple Command Button from the Forms control menu onto Sheet2, and link it to the following Macro (in a standard code mode):


Public Sub Main()
Dim oMaster As Workbook
Dim oSlave As Workbook

Dim sSlaveName As String
sSlaveName = "C:\Users\jwhite31\Desktop\Slave.xlsb"

Set oMaster = ActiveWorkbook
Set oSlave = Application.Workbooks.Open(sSlaveName, 0, True)

oMaster.Sheets("Sheet1").Delete

oSlave.Close
Set oSlave = Nothing
Set oMaster = Nothing
End Sub

+ Slave.xlsm is just an empty worksheet with a couple of empty tabs; nothing else

Then, press the command button to run the code; all works as expected; Sheet1 has been deleted from the Master file. Now try to save Master.xlsm - on my PC this crashes every time!

What is going on? I hope someone has some ideas ...

snb
03-10-2014, 06:25 AM
In which codemodule has this macro been stored ?

Torchwood
03-10-2014, 06:37 AM
Just a standard module (in VB Editor, Insert / Module) ...

Kenneth Hobs
03-10-2014, 06:47 AM
Remove the last two lines that set the workbooks to Nothing.

Torchwood
03-10-2014, 07:02 AM
Not sure why? Anyway; tested that - no difference.

Here is something strange - repeat the above whole test, but use an ActiveX command button instead of a Form button. Now it all works fine, with no crash (which suggests the code is fine)
BUT - now add a Form commandbutton back on to Sheet2 and link it to a "Hello World" sub - the crash is back when you press the ActiveX button. So, it is something about the form control itself that Excel doesn't like ...

Aflatoon
03-10-2014, 07:04 AM
That's got to be a bug. I can reproduce that here, so I'll file it.

Kenneth Hobs
03-10-2014, 07:12 AM
I seldom find a need for Form controls. I prefer ActiveX controls. Of course your ActiveX control's code will be in the sheet object.

Torchwood
03-10-2014, 07:25 AM
I have avoided Form controls in the past because of problems with them resizing when using laptops/projectors (I have 2 links to demonstrate that problem, but don't seem to be able to include them here).

This is very inconvenient - so I used Form controls which I had thought a bit more robust. However, not as robust as I thought ... :(

Aflatoon
03-10-2014, 07:25 AM
Courtesy of Andy Pope: the workaround is to activate the master workbook before deleting the sheet.

Torchwood
03-10-2014, 07:26 AM
Thanks Aflatoon ... good to know its not just me!

Torchwood
03-10-2014, 07:28 AM
I just tried adding oMaster.Activate at the end of the macro; still crashes ...

Aflatoon
03-10-2014, 07:51 AM
Where did you put it - it has to be before you delete the sheet.

Torchwood
03-10-2014, 08:54 AM
Very interesting - that did work! (I put the Activate directly after the Workbooks.Open). So ... problem solved!

Kenneth Hobs
03-10-2014, 11:55 AM
Not all commands need the sheet or workbook to be active but you found a case where it is. It is surprising that the ActiveX control did not cause the issue. I think that was a fluke though.

snb
03-10-2014, 03:45 PM
I had no problems using this code:


Sub M_Main()
With GetObject("G:\OF\slave.xlsb")
ThisWorkbook.Sheets("sheet1").Delete
.Close
End With
End Sub

Form button in thisworkbook.sheets("sheet1")

Macro M_Main in codemodule Sheets("Sheet1")

- a warning at the deletion of the sheet

when closing thisworkbook (master.xlsm), no errors nor crashes.