excel2003 delete sheet from VBA

[nodari]

I need to delete a sheet from my workbook from VBA. I do like this:

Application.DisplayAlerts = False
Sheets("sheet3").Delete
Application.DisplayAlerts = true

but after deleting it gives me alert: can't enter break mode at this time

need that the sheet be deleted without alerting.

 

[jeffreybrown]

The code looks fine and work good for me.

Are you sure there isn't anything else getting in the way?

What if you run the code below, what happens?

I don't have 2003 here but this should still work.

Sub DelSht()
    Application.DisplayAlerts = False
    Sheets("sheet3").Delete
    Application.DisplayAlerts = True
End Sub

 

[nodari]

The code looks fine and work good for me.

Are you sure there isn't anything else getting in the way?

What if you run the code below, what happens?

I don't have 2003 here but this should still work.

Sub DelSht()
    Application.DisplayAlerts = False
    Sheets("sheet3").Delete
    Application.DisplayAlerts = True
End Sub

I have exactly so.

now I checked again and seemed it makes this alert when the sheet is very big, nearly 5MB XLS file and main information is in this sheet I want to delete.

so, now the problem look like so: what to do for to delete big sheet and not to get the alert!

 

[bjurney]

I simulated the situation, and I am using 2003, and made a sheet that was 104 MB. The code works fine without alerting me. I would scan through your code and make sure that you dont have this line until the end of your code

Application.DisplayAlerts = True

 

[jeffreybrown]

Just like bjurney I tested with a rather large workbook and the code worked fine.

Sorry cannot recreate your problem.

 

[nodari]

it seems it needs more experience to find what kind problem do I have and if find any I'll write here.

 

[nodari]

Hi

I checked many times, but I didn't find the reason. after

sheets(sheet3").delete

it deletes this sheet, but after says that can't get in break mode. why does it try this?

I found the reason is TextBox put on the sheet (used for filtering data).

but anyway, not clear, why does it make problem?

 

[nodari]

I checked now and want to add, that the TextBox is designed from Macro Design Mode.

 

[VoG]

Try

With CommandBars("Exit Design Mode").Controls(1)
    If .State = msoButtonDown Then .Execute
End With
Application.DisplayAlerts = False
Sheets("sheet3").Delete
Application.DisplayAlerts = True

 

[nodari]

Try

With CommandBars("Exit Design Mode").Controls(1)
    If .State = msoButtonDown Then .Execute
End With
Application.DisplayAlerts = False
Sheets("sheet3").Delete
Application.DisplayAlerts = True

no, it doesn't solve the problem. it would be help, if the problem was on design mode's active mode. but the design mode is not active . . .


and here I found one more problem for me: I don't know how it is put the textBox from design mode. I tryed to do same box by myself now, but I didn't find way, how to do . . .