Entering Debug after delete sheet

[DocBrown]

I have a macro where it's tasks is to delete a WS and copy a replacement from
another WB. When single stepping through the code, after the
Sheets("Lists").delete, VBA will no longer allow me to enter the debug mode.
I get the message "Can't enter debug mode at this time."

Any ideas why this occurs? And how to I enter debug in this code? (other
than debug.print)

In order for the rest of the code to operate correctly, the sheet must be
deleted.

Thanks,
John

 

[Dave Peterson]

I've never seen this occur after deleting a sheet.

But I have seen a similar message appear "Can't enter break mode at this time"
when I'm adding a control from the Control toolbox toolbar to a worksheet. I
can't step through that line (always????).

So I set a break point right before and after and just run to the next step.
It's almost the same.

(Are you sure it's "debug mode" in the warning message???)

 

[DocBrown]

You are correct, it is '... break mode ...'

Break points encounter the same message. The only enabled options buttons
are Continue or End.

The code in question is:

prevValue = Application.DisplayAlerts
Application.DisplayAlerts = False
On Error Resume Next
Sheets.Item("Lists").Delete
On Error GoTo ErrThisSub
Application.DisplayAlerts = prevValue

The sheet is deleted as expected. I'm trying to debug a problem that occurs
after this point in the code.

John S

 

[Dave Peterson]

Try putting a break point before the offending line. And another after the
offending line.

Then don't step through the code after you hit that first break point--Run it.

 

[DocBrown]

Did that. VBA recognizes the break point but displays that message. Can't
enter break mode at this time.

prevValue = Application.DisplayAlerts
Application.DisplayAlerts = False
On Error Resume Next <-- BreakPoint, stops here.
Sheets.Item("Lists").Delete
On Error GoTo ErrThisSub <-- BreakPoint. Message displayed?
Application.DisplayAlerts = prevValue

I hit continue, and my code encounters another failure I'm trying to locate
the source of the failure. The error I encounter Error 70' Permission
Denied.' And VBA will not allow entry into debug/break mode.

John

 

[Dave Peterson]

I got the same error that you got.

But this syntax worked fine (even stepping through the code):
Sheets("Lists").Delete

And then I tested your syntax again and it worked fine--with or without the
Lists sheet existing.

So I closed excel, reopened excel, and started testing both versions. I
couldn't break either.

I don't have a guess.

Have you closed excel and reopened???

Did that. VBA recognizes the break point but displays that message. Can't
enter break mode at this time.

prevValue = Application.DisplayAlerts
Application.DisplayAlerts = False
On Error Resume Next <-- BreakPoint, stops here.
Sheets.Item("Lists").Delete
On Error GoTo ErrThisSub <-- BreakPoint. Message displayed?
Application.DisplayAlerts = prevValue

I hit continue, and my code encounters another failure I'm trying to locate
the source of the failure. The error I encounter Error 70' Permission
Denied.' And VBA will not allow entry into debug/break mode.

John

 

[DocBrown]

Thanks a lot for testing that. What that indicates to me is that there is
some other factor in my code that I didn't provide that is contributing to
the symptom I'm encountering. I think I know what it might be. I hope this is
not too much data.

On the 'Lists' sheet is a command button. The command button's click event
is coded as:

Private Sub cmdBuildLists_Click()
BuildLists
End Sub

The routine BuildLists exists in the Module1 code. The BuildLists routine
manipulates data on the 'Lists' worksheet' and creates Named Ranges.

On another WS 'Budget Template' is the following macro to respond to another
command button:

'Budget Template':
Private Sub cmdMigrateWB_Click()
' Macro created 2/24/2009 by John
' This macro is called by the command button on the template
' worksheet in order to initiate the migration process or
' template update process.

If ThisWorkbook.FileFormat = xlTemplate Then
' we are obtaining the 'Lists' sheet from the previous template
CheckLists (True)
Else
MigrateWorkbook
End If
End Sub


Also in Module1 is the routine that is failing and has the following partial
logic:

Sub CheckLists(Optional tmplUpdate As Boolean = False)

' Gather some data from 'Lists' worksheet,
' prompt the user to open another template,
' verify the file that is opened is what I expect and has the correct
version ids.
' Do this:

prevValue = Application.DisplayAlerts
Application.DisplayAlerts = False
On Error Resume Next <-- BreakPoint, stops here.
Sheets.Item("Lists").Delete
On Error GoTo ErrThisSub <-- BreakPoint. Message displayed?
Application.DisplayAlerts = prevValue

'copy the template from the other workbook.
' close the other workbook.

So I think the issue is that VBA does not like me to delete the sheet that
has a macro that is calling another macro in Module1.

So to avoid this issue, I have a question. Is there a way to intercept a
command button event from a macro that is not on the sheet with the command
button. I think if I can get that code off of the sheet, I will avoid this
issue. Is there another way to do this? The other option is I just accept
that I can't step through this code to debug.

I submitted another message here for a similar issue. In that case, I was
deleting the sheet that contained the command button that called the Module1
macro.

Psudo code:

Sheet2:
Private Sub cmd_Click()
DeleteSheet
end sub

Module1:
sub DeleteSheet()
Sheet2.delete
end sub.

This seems to run fine, but after the Sheet2.delete, I couldn't enter break
mode. I need to delete those sheets, but I don't know how to do this in a
clean way. I thought in this case, I wouldn't hit the problem because I
wasn't deleting the macro that Module1 macro was trying to return to.

Any thoughts?

Thanks,
John

 

[Dave Peterson]

I don't think it's the code. I put some controls from the control toolbox on a
worksheet with no code.

I had the same problem with no code in the worksheet module.

Thanks a lot for testing that. What that indicates to me is that there is
some other factor in my code that I didn't provide that is contributing to
the symptom I'm encountering. I think I know what it might be. I hope this is
not too much data.

On the 'Lists' sheet is a command button. The command button's click event
is coded as:

Private Sub cmdBuildLists_Click()
BuildLists
End Sub

The routine BuildLists exists in the Module1 code. The BuildLists routine
manipulates data on the 'Lists' worksheet' and creates Named Ranges.

On another WS 'Budget Template' is the following macro to respond to another
command button:

'Budget Template':
Private Sub cmdMigrateWB_Click()
' Macro created 2/24/2009 by John
' This macro is called by the command button on the template
' worksheet in order to initiate the migration process or
' template update process.

If ThisWorkbook.FileFormat = xlTemplate Then
' we are obtaining the 'Lists' sheet from the previous template
CheckLists (True)
Else
MigrateWorkbook
End If
End Sub

Also in Module1 is the routine that is failing and has the following partial
logic:

Sub CheckLists(Optional tmplUpdate As Boolean = False)

' Gather some data from 'Lists' worksheet,
' prompt the user to open another template,
' verify the file that is opened is what I expect and has the correct
version ids.
' Do this:

prevValue = Application.DisplayAlerts
Application.DisplayAlerts = False
On Error Resume Next <-- BreakPoint, stops here.
Sheets.Item("Lists").Delete
On Error GoTo ErrThisSub <-- BreakPoint. Message displayed?
Application.DisplayAlerts = prevValue

'copy the template from the other workbook.
' close the other workbook.

So I think the issue is that VBA does not like me to delete the sheet that
has a macro that is calling another macro in Module1.

So to avoid this issue, I have a question. Is there a way to intercept a
command button event from a macro that is not on the sheet with the command
button. I think if I can get that code off of the sheet, I will avoid this
issue. Is there another way to do this? The other option is I just accept
that I can't step through this code to debug.

I submitted another message here for a similar issue. In that case, I was
deleting the sheet that contained the command button that called the Module1
macro.

Psudo code:

Sheet2:
Private Sub cmd_Click()
DeleteSheet
end sub

Module1:
sub DeleteSheet()
Sheet2.delete
end sub.

This seems to run fine, but after the Sheet2.delete, I couldn't enter break
mode. I need to delete those sheets, but I don't know how to do this in a
clean way. I thought in this case, I wouldn't hit the problem because I
wasn't deleting the macro that Module1 macro was trying to return to.

Any thoughts?

Thanks,
John

 

[DocBrown]

I really appreciate that you figured that out. I just confirmed your results.

I might not have completely characterized this, but it appears that if there
are any command buttons in the workbook and then you delete any worksheet,
then VBA can't enter break mode.

Do you have any ideas how to workaround this?

 

[Dave Peterson]

I was screwing around with deleting the oleobjects first, but that didn't seem
to help at all.

But I did run into a weird behavior.

I had two workbooks open and was running code in one workbook's project and
deleting the sheet (with OLEObjects) in the other and I could step through it
with no problem.

My work-arounds would be:
Don't step through the code (it works fine when running)

Delete the worksheet in a separate procedure that does nothing else (or manually
delete it.

or run a macro from a different workbook (develop/debug the macro in a separate
workbook and move it to its real home when you're ready).

I understand that it's a pain to debug this way, I'm just glad it works ok when
the code is really running.

I really appreciate that you figured that out. I just confirmed your results.

I might not have completely characterized this, but it appears that if there
are any command buttons in the workbook and then you delete any worksheet,
then VBA can't enter break mode.

Do you have any ideas how to workaround this?

 

[DocBrown]

It looks like I can workaround the problem while debugging by manually
deleting the worksheet at the point the macro would do it.

I, too, am glad that at least the code runs correctly. Actually, I'm very
surprised that the macro in Module1 will continue to run to completion even
when I delete the WS that contains the macro that called the routine in
Module1. I would bet that the WS delete doesn't actually complete until the
WS macro has exited.

I just might test that. *grin*

Thanks for your ideas on how to approach this.
John

 

[Dave Peterson]

Sometimes, it's just best to lower your standards <vbg>.

It looks like I can workaround the problem while debugging by manually
deleting the worksheet at the point the macro would do it.

I, too, am glad that at least the code runs correctly. Actually, I'm very
surprised that the macro in Module1 will continue to run to completion even
when I delete the WS that contains the macro that called the routine in
Module1. I would bet that the WS delete doesn't actually complete until the
WS macro has exited.

I just might test that. *grin*

Thanks for your ideas on how to approach this.
John

 

[DocBrown]

Yup, The code in the WS module runs to completion even if you call
Sheet?.delete in the Module1 macro that was called by that WS. Although it
seems that any references made to the deleted sheet will fail.

It looks like I can workaround the problem while debugging by manually
deleting the worksheet at the point the macro would do it.

I, too, am glad that at least the code runs correctly. Actually, I'm very
surprised that the macro in Module1 will continue to run to completion even
when I delete the WS that contains the macro that called the routine in
Module1. I would bet that the WS delete doesn't actually complete until the
WS macro has exited.

I just might test that. *grin*

Thanks for your ideas on how to approach this.
John