I have an existing workbook (WrkBk1) that contains two sheets. I also
have an addin (addin) that inserts a new sheet into WrkBk1. When the
user is done with the workbook I want it to delete the sheet that was
added in, then save and then close both WrkBk1 and the addin. Here is
the code that I am using to initiate the activity.

Private Sub workbook_beforeclose(cancel As Boolean)
Application.DisplayAlerts = False
ActiveWorkbook.Sheets("What If").Delete
Application.DisplayAlerts = True
ActiveWorkbook.Save
End Sub

It works fine if it is saved into WrkBk1 but I can't do that I need
everything to be contained in the addin. Right now it is saved into a
module, I'm not sure if that makes a difference or not.

Hi!

I'm not sure what you need, but try this one.

Private Sub workbook_beforeclose(cancel As Boolean)
Application.DisplayAlerts = False
Workbooks("YourWorkbookName").Sheets("What If").Delete
Application.DisplayAlerts = True
ActiveWorkbook.Save
End Sub

Regards,
Kari J Keinonen

Personally, I think it can be a problem to save the workbook -- if the user
opens the workbook, makes some disasterous changes to the other sheet and wants
to close without saving, your code could really hurt.

I'd either delete that worksheet right before the addin needs to add it or
delete it when the workbook opens.

Option Explicit
sub Auto_Open()
application.displayalerts = false
on error resume next
thisworkbook.worksheets("what if").delete
on error goto 0
application.displayalerts = true
end sub

You could use similar code in the addin, but change this line:
thisworkbook.worksheets("what if").delete
to:
activeworkbook.worksheets("what if").delete


stewdizzle wrote:
>
> I have an existing workbook (WrkBk1) that contains two sheets. I also
> have an addin (addin) that inserts a new sheet into WrkBk1. When the
> user is done with the workbook I want it to delete the sheet that was
> added in, then save and then close both WrkBk1 and the addin. Here is
> the code that I am using to initiate the activity.
>
> Private Sub workbook_beforeclose(cancel As Boolean)
> Application.DisplayAlerts = False
> ActiveWorkbook.Sheets("What If").Delete
> Application.DisplayAlerts = True
> ActiveWorkbook.Save
> End Sub
>
> It works fine if it is saved into WrkBk1 but I can't do that I need
> everything to be contained in the addin. Right now it is saved into a
> module, I'm not sure if that makes a difference or not.

--

Dave Peterson

You would need to have the addin instantiate application level events.

see Chip Pearson's page on this

http://www.cpearson.com/excel/appevent.htm


It would then need to check the workbook triggering the before close event
and make sure it is the one that needs the sheet deleted.

You could have


Private Sub WorkbookBeforeClose(Wb As Workbook, Cancel As Boolean)
if wb.name = Thisworkbook.Name then exit sub
if wb.Name = "Something" then ' some condition to check the workbook
Application.DisplayAlerts = False
wb.Sheets("What If").Delete
Application.DisplayAlerts = True
wb.Save
ThisWorkbook.Close
End if
End Sub


--
Regards,
Tom Ogilvy




"stewdizzle" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have an existing workbook (WrkBk1) that contains two sheets. I also
> have an addin (addin) that inserts a new sheet into WrkBk1. When the
> user is done with the workbook I want it to delete the sheet that was
> added in, then save and then close both WrkBk1 and the addin. Here is
> the code that I am using to initiate the activity.
>
> Private Sub workbook_beforeclose(cancel As Boolean)
> Application.DisplayAlerts = False
> ActiveWorkbook.Sheets("What If").Delete
> Application.DisplayAlerts = True
> ActiveWorkbook.Save
> End Sub
>
> It works fine if it is saved into WrkBk1 but I can't do that I need
> everything to be contained in the addin. Right now it is saved into a
> module, I'm not sure if that makes a difference or not.
>