Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addin close command not working in new workbook
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addin close command not working in new workbook
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addin close command not working in new workbook
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addin close command not working in new workbook
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" wrote in message ups.com... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to close an Addin? | Excel Discussion (Misc queries) | |||
VBA Code to kick off macro when workbook command to close is initi | Excel Discussion (Misc queries) | |||
Deleting Command Bars on close of a workbook | Excel Programming | |||
Executinh Addin Sub Menu Command | Excel Programming | |||
Addin Close problem | Excel Programming |