I have a sheet that has a command button. One of the tasks I want the macro
do to is delete the sheet that the command button is on. What is the proper
way to do that?

What do you think of this:

On Sheet3 code:

Private Sub cmdMigrateWB_Click()
MigrateWorkbook
end sub

In Module1:

Sub MigrateWorkbook()

On Error GoTo ErrThisSub

' Do a bunch of stuff

prevValue = Application.DisplayAlerts
Application.DisplayAlerts = False
On Error Resume Next

'delete the sheet the cmd button was on
Sheets(3).Delete
On Error GoTo ErrThisSub
Application.DisplayAlerts = prevValue

' do some more stuff

Exit Sub

ErrThisSub:
MsgBox "Error in routine."

End Sub

It seems to work, but it seems wrong to delete the code that the Module1
macro is trying to return to. The main effect I've seen is that I can't enter
debug after the delete has occured.

Suggestions?

Thanks,
John

It may be deleting the wrong worksheet. Try deleting the worksheet by its
name instead of the number

Sheets("sheet3").Delete


"DocBrown" wrote:

> I have a sheet that has a command button. One of the tasks I want the macro
> do to is delete the sheet that the command button is on. What is the proper
> way to do that?
>
> What do you think of this:
>
> On Sheet3 code:
>
> Private Sub cmdMigrateWB_Click()
> MigrateWorkbook
> end sub
>
> In Module1:
>
> Sub MigrateWorkbook()
>
> On Error GoTo ErrThisSub
>
> ' Do a bunch of stuff
>
> prevValue = Application.DisplayAlerts
> Application.DisplayAlerts = False
> On Error Resume Next
>
> 'delete the sheet the cmd button was on
> Sheets(3).Delete
> On Error GoTo ErrThisSub
> Application.DisplayAlerts = prevValue
>
> ' do some more stuff
>
> Exit Sub
>
> ErrThisSub:
> MsgBox "Error in routine."
>
> End Sub
>
> It seems to work, but it seems wrong to delete the code that the Module1
> macro is trying to return to. The main effect I've seen is that I can't enter
> debug after the delete has occured.
>
> Suggestions?
>
> Thanks,
> John

If your delete code is in the sheet code module of the deleted sheet, you
will not be able to return to the code because you will have deleted it.
Put the code in the project module (Module1) and it should then allow you to
return to the code after the delete event.



"DocBrown" <(E-Mail Removed)> wrote in message
news:344068E2-C487-420E-9C54-(E-Mail Removed)...
>I have a sheet that has a command button. One of the tasks I want the macro
> do to is delete the sheet that the command button is on. What is the
> proper
> way to do that?
>
> What do you think of this:
>
> On Sheet3 code:
>
> Private Sub cmdMigrateWB_Click()
> MigrateWorkbook
> end sub
>
> In Module1:
>
> Sub MigrateWorkbook()
>
> On Error GoTo ErrThisSub
>
> ' Do a bunch of stuff
>
> prevValue = Application.DisplayAlerts
> Application.DisplayAlerts = False
> On Error Resume Next
>
> 'delete the sheet the cmd button was on
> Sheets(3).Delete
> On Error GoTo ErrThisSub
> Application.DisplayAlerts = prevValue
>
> ' do some more stuff
>
> Exit Sub
>
> ErrThisSub:
> MsgBox "Error in routine."
>
> End Sub
>
> It seems to work, but it seems wrong to delete the code that the Module1
> macro is trying to return to. The main effect I've seen is that I can't
> enter
> debug after the delete has occured.
>
> Suggestions?
>
> Thanks,
> John

The Sub MigrateWorkbook that is doing all the work is in the Module1 code.
The sheet being deleted only has a call to the Sub in Module1:

Private Sub cmdMigrateWB_Click()
MigrateWorkbook
end sub


This issue is that I need to operate on the Click event of the command
button that is on the sheet that will be deleted. Is there another way to
intercept the click event of the command button on the worksheet?

"JLGWhiz" wrote:

> If your delete code is in the sheet code module of the deleted sheet, you
> will not be able to return to the code because you will have deleted it.
> Put the code in the project module (Module1) and it should then allow you to
> return to the code after the delete event.
>
>
>
> "DocBrown" <(E-Mail Removed)> wrote in message
> news:344068E2-C487-420E-9C54-(E-Mail Removed)...
> >I have a sheet that has a command button. One of the tasks I want the macro
> > do to is delete the sheet that the command button is on. What is the
> > proper
> > way to do that?
> >
> > What do you think of this:
> >
> > On Sheet3 code:
> >
> > Private Sub cmdMigrateWB_Click()
> > MigrateWorkbook
> > end sub
> >
> > In Module1:
> >
> > Sub MigrateWorkbook()
> >
> > On Error GoTo ErrThisSub
> >
> > ' Do a bunch of stuff
> >
> > prevValue = Application.DisplayAlerts
> > Application.DisplayAlerts = False
> > On Error Resume Next
> >
> > 'delete the sheet the cmd button was on
> > Sheets(3).Delete
> > On Error GoTo ErrThisSub
> > Application.DisplayAlerts = prevValue
> >
> > ' do some more stuff
> >
> > Exit Sub
> >
> > ErrThisSub:
> > MsgBox "Error in routine."
> >
> > End Sub
> >
> > It seems to work, but it seems wrong to delete the code that the Module1
> > macro is trying to return to. The main effect I've seen is that I can't
> > enter
> > debug after the delete has occured.
> >
> > Suggestions?
> >
> > Thanks,
> > John
>
>
>