Deleting sheet => abrupt end to execution (Excel VBA) 1

[krinid]

After deleting an Excel sheet with a button (control button from Control Toolbox not Button from Drawing) on it via VBA, all currently loaded forms unload, as if I hit the 'STOP' execution button from the editor. Deleting the sheet manually causes no such reaction. Execution only seems to stop when the sheet being deleted contains an object from the Control Toolbox; objects from the Drawing toolbox have no problem.

Any ideas?

Details:
1) the sub which actually deletes the sheet is NOT connected to the sheet in any way save that it resides in the same workbook
2) the actual code is simply: WS.Delete (WS is the appropriate WS object)
3) Delete the sheet from the immediate window has the same effect (stops all execution). Code used: Sheets("SheetName&quot.Delete (Answer "y" to 'Are you sure?')
4) the sheet being deleted contains several functions/subs and several button objects on it; deleting other sheets does not have the same effect (execution continues)
5) I've replicated this effect by creating a form in a new book with a single button deleting sheets(1); when sheets(1) has a Command Button, the form unloads; when sheets(1) doesn't, the form stays loaded; there's no problem in deleting sheets with Button objects on them (Drawing toolbar); in fact, I've only mentioned buttons here, but if a sheet has any object from the Control toolbox, it stops execution upon deletion

Anyone ever experienced this?
Is there a way around it? (without using drawing toolbar objects)
Is this just a 'feature' of Excel? (I tend to think not, since behavior is different from VBA vs. manual)

 

[BrainB]

I have given up using objects from the Controls toolbar in worksheets because they are completely bug-ridden - giving rise to unpredictable actions such as you mention, which, although seemingly unrelated, disappear when you use controls from the Forms toolbar.



Regards
BrianB
** Let us know if you get something that works !
================================

 

[combo]

Activex controls placed on a sheet have their event procedures ("click", "change" etc.) in a code module linked with the sheet, so deleting the sheet you also delete a part of the VBA project. This may interfere with running code.
BTW, how can you work with immediate window while code is running?

combo

 

[krinid]

BrianB,
I may give up on them too! What other oddities have you come across with controls toolbar objects? This may speed up my decision to stop using them.

Combo,
Hm. Good point. As for the immediate window, code isn't actually being executed while I'm using the immediate window, but rather that code is live (ie: userform is active, at a breakpoint, etc).

 

[TonyJollans]

Hi krinid,

You're having fun digging up some issues, aren't you?

I can recreate this (in Excel 2000) when the button on the sheet has code. If I set a break on the Delete statement and step forward I get a VBA message "Can't enter Break mode at this time" with the options "Continue" and "End"; whichever I choose the Sheet is still deleted. It only fails when the code runs through without a break.

I don't have anything else to add - combo's explanation makes sense but some kind of warning would be nice.

Enjoy,
Tony

 

[krinid]

Tony, I agree, a warning would be nice. Or at least documentation, or anything, even. And yes, I am having fun digging up issues. In the environment I work in there's absolutely nobody around who I can talk to about such issues, and thus my only form of intellectual programming conversation is this board! What did I ever do before I found it ?

Tony, Combo,
It seems combo's right indeed... up to now I've only created these buttons on the fly from the immediate window. Either creating or deleting them from code causes execution to halt. Mind you, it finishes the subroutine it's executing before execution is halted. You can't put in a break or a STOP ("Can't enter Break mode at this time" error occurs, as you noted), but you can do a MsgBox, and while it's displayed, all forms are still active. The userform is normally halted, too, properly going through the UserForm_Terminate event. Same thing happens if I try to 'cut' the buttons, too.

What I don't understand is that a worksheet is a property of the project, too, but deleting a worksheet doesn't cause execution to halt. There must be some other reason for it. (Possibly a bug or just an unfriendly 'feature').

Thus the next phase of this issue:
can anyone think of a way to stop the form for being halted?

I think I'd probably be better off taking BrainB's advice of abandoning command buttons for buttons. But I'll throw this out as a mental challenge, if nothing else.

 

[VBAjedi]

I don't have the time to set this up and test it, but why can't you just delete all controls from the sheet before you delete the sheet? That seems like it would be straightforward enough. . .

VBAjedi

 

[TonyJollans]

Hi VBAjedi,

That appears to work but can you explain it?

When the control is deleted, the code remains behind in the sheet's code module which only gets deleted when the sheet itself is deleted.

combo's (plausible) explanation of the original problem, that removing part of the VBA Project somehow destabilised running code should still stand either at shape delete or sheet delete, shouldn't it?

Enjoy,
Tony

 

[VBAjedi]

Well, deleting a sheet triggers some of the events for the controls on that sheet. With the control gone, it doesn't trigger any of the events when you delete the sheet, and the associated code doesn't get called. The code only causes problems when something calls it to run after the sheet no longer exists.

Glad that worked for you!

VBAjedi

 

[krinid]

VBAjedi, Tony,
I tried deleting the objects first (Sheet.OLEobject, but as soon as an object is deleted, the same thing happens (all forms close). Did either of you have a different result?

One more thing, not only do forms close upon deleting the button object, after restarting the form and deleting the sheet which no longer has the button on it, the forms still close. Only sheets which never had an OLEobject are safe to delete.

 

[TonyJollans]

Hi krinid,

When I tested deleting the button first I used Shapes(1).Delete, not OLEObjects(1).Delete, but ...

... try as I might I cannot recreate the problem this morning at all. The joys of Excel!

Enjoy,
Tony

 

[krinid]

Tony,
Hm, I seem to have the opposite problem; whatever I do seems to generate the problem, whether I use Shapes(1).Delete or OLEObjects(1).Delete
Looks like I'll save a lot of hair (my own and other peoples' too) by just using a regular old button!

 

[combo]

Hi,
Activex objects placed on a sheet have their event code in the sheet's code module, while macro for drawing object (click event only) is located in standard module - there is the difference.
I've just did the following:
1. added userform with a button deleting sheet1,
2. added a button (activex) on sheet1, with a code displaying the userform.
The sheet1 was deleted and nothing happened with the form. I put the Msgbox just after UserForm1.Show code, and it was displayed. But when I called a sub to show test message instead, it was not displayed.

Krinid,
I think there is the problem - too much code in deleted sheet's module. It stops (disappears) without warning. And nowhere to return.
So, I would rather transfer crucial code to standard VBA components, or leave in sheet's modules only in case when it never be deleted. When the sheet is to be deleted, it should be the single action, without calling anything after in the code.

combo

 

[krinid]

Good info--thanks!

Exactly how did you manage to get the form not to unload? I'm not quite clear on that.

In my case, the code which deletes the sheet is in a sub within the form which unloads. The sub itself is activated by clicking a button on the same form. But somehow in my case, the form unloads.

This sounds the same as your experiment, a form with a button deleting a sheet with had an activeX button. But different results. hmmm Have I missed something?

 

[combo]

Hi Krinid,
This is my test code:
UserForm1 with CommandButton1:
[tt]Private Sub CommandButton1_Click()
Worksheets("Sheet1&quot.Delete
End Sub[/tt]

Worksheet named Sheet1 with CommandButton1, clicked to display the userform:
Option 1 (massage is displayed):
[tt]Private Sub CommandButton1_Click()
UserForm1.Show
MsgBox "Sheet deleted!"
End Sub[/tt]

Option 2 (message not displayed):
[tt]Private Sub CommandButton1_Click()
UserForm1.Show
Call info
End Sub

Sub info
MsgBox "Sheet deleted!"
End Sub[/tt]

Thanks for the star!

combo