Eenie meenie minee......
I have three Workbooks and all need to be open at once.
The one required at any given time is selected using "Window" in the Menu Bar. Each Workbook has an associated and unique Custom Toolbar brought up when the Workbook is first opened, using "Auto_Open". To close each WB there is a button on its associated toolbar which operates a WB close module (macro). There are two things I cannot fathom how to do. 1. When I select a different WB (ie one of the other two via "Window") I need its associated unique toolbar to be displayed and that of the parting WB removed. 2. When I close a given WB, using its unique toolbar's close button, I need that toolbar to be removed and replaced by the one associated with the (next) WB now appearing. I can achieve 1 above by putting suitable code in ThisWorkbook, Workbook Activate. However when I do 2 above I get an error message "Application-defined or object-defined error" I can't find a suitable event kicked off by the closure of the "old" WB and the arrival of the "new" Any help or suggestions would be greatly appreciated. Don |
Eenie meenie minee......
I think you need to trap application events. You can do this by adding a
class module with the following in the declarations: Public WithEvents xlApp As Application You also need to declare this in a standard module: Public xlApplication As New clsAppEvents And this in the ThisWorkbook object: Private Sub Workbook_Open() Set xlApplication.xlApp = Application End Sub This will expose application events in the class module, one of which being xlApp_Workbook_Activate. You can then do whatever you wish based on the workbook that is open (select xlApp in the object dropdown at the the top of the VBE - the possible procedures are then listed in the procedure dropdown). Cheers, Jeff "don bowyer" wrote in message ... I have three Workbooks and all need to be open at once. The one required at any given time is selected using "Window" in the Menu Bar. Each Workbook has an associated and unique Custom Toolbar brought up when the Workbook is first opened, using "Auto_Open". To close each WB there is a button on its associated toolbar which operates a WB close module (macro). There are two things I cannot fathom how to do. 1. When I select a different WB (ie one of the other two via "Window") I need its associated unique toolbar to be displayed and that of the parting WB removed. 2. When I close a given WB, using its unique toolbar's close button, I need that toolbar to be removed and replaced by the one associated with the (next) WB now appearing. I can achieve 1 above by putting suitable code in ThisWorkbook, Workbook Activate. However when I do 2 above I get an error message "Application-defined or object-defined error" I can't find a suitable event kicked off by the closure of the "old" WB and the arrival of the "new" Any help or suggestions would be greatly appreciated. Don |
Eenie meenie minee......
In ThisWorkbook module use the Activate event sub to create the menu, and
the Deactivate event to delete the menu. Mike F "don bowyer" wrote in message ... I have three Workbooks and all need to be open at once. The one required at any given time is selected using "Window" in the Menu Bar. Each Workbook has an associated and unique Custom Toolbar brought up when the Workbook is first opened, using "Auto_Open". To close each WB there is a button on its associated toolbar which operates a WB close module (macro). There are two things I cannot fathom how to do. 1. When I select a different WB (ie one of the other two via "Window") I need its associated unique toolbar to be displayed and that of the parting WB removed. 2. When I close a given WB, using its unique toolbar's close button, I need that toolbar to be removed and replaced by the one associated with the (next) WB now appearing. I can achieve 1 above by putting suitable code in ThisWorkbook, Workbook Activate. However when I do 2 above I get an error message "Application-defined or object-defined error" I can't find a suitable event kicked off by the closure of the "old" WB and the arrival of the "new" Any help or suggestions would be greatly appreciated. Don |
Eenie meenie minee......
Jeff.
tip: since thisworkbook is a classmodule you could put them all in thisworkbook... I often use A as it's name, so i can use things like a.goto a.worksheetfunction etc. Option Explicit Public WithEvents A As Application Private Sub A_SheetActivate(ByVal Sh As Object) A.Goto Sh.Cells(3, 3) End Sub Private Sub Workbook_Open() Set A = Application End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Jeff Standen" wrote: I think you need to trap application events. You can do this by adding a class module with the following in the declarations: Public WithEvents xlApp As Application You also need to declare this in a standard module: Public xlApplication As New clsAppEvents And this in the ThisWorkbook object: Private Sub Workbook_Open() Set xlApplication.xlApp = Application End Sub This will expose application events in the class module, one of which being xlApp_Workbook_Activate. You can then do whatever you wish based on the workbook that is open (select xlApp in the object dropdown at the the top of the VBE - the possible procedures are then listed in the procedure dropdown). Cheers, Jeff "don bowyer" wrote in message ... I have three Workbooks and all need to be open at once. The one required at any given time is selected using "Window" in the Menu Bar. Each Workbook has an associated and unique Custom Toolbar brought up when the Workbook is first opened, using "Auto_Open". To close each WB there is a button on its associated toolbar which operates a WB close module (macro). There are two things I cannot fathom how to do. 1. When I select a different WB (ie one of the other two via "Window") I need its associated unique toolbar to be displayed and that of the parting WB removed. 2. When I close a given WB, using its unique toolbar's close button, I need that toolbar to be removed and replaced by the one associated with the (next) WB now appearing. I can achieve 1 above by putting suitable code in ThisWorkbook, Workbook Activate. However when I do 2 above I get an error message "Application-defined or object-defined error" I can't find a suitable event kicked off by the closure of the "old" WB and the arrival of the "new" Any help or suggestions would be greatly appreciated. Don |
Eenie meenie minee......
Heh, this shows what happens when you read stuff out of a book without
really understanding it :) Jeff "keepITcool" wrote in message ... Jeff. tip: since thisworkbook is a classmodule you could put them all in thisworkbook... I often use A as it's name, so i can use things like a.goto a.worksheetfunction etc. Option Explicit Public WithEvents A As Application Private Sub A_SheetActivate(ByVal Sh As Object) A.Goto Sh.Cells(3, 3) End Sub Private Sub Workbook_Open() Set A = Application End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Jeff Standen" wrote: I think you need to trap application events. You can do this by adding a class module with the following in the declarations: Public WithEvents xlApp As Application You also need to declare this in a standard module: Public xlApplication As New clsAppEvents And this in the ThisWorkbook object: Private Sub Workbook_Open() Set xlApplication.xlApp = Application End Sub This will expose application events in the class module, one of which being xlApp_Workbook_Activate. You can then do whatever you wish based on the workbook that is open (select xlApp in the object dropdown at the the top of the VBE - the possible procedures are then listed in the procedure dropdown). Cheers, Jeff "don bowyer" wrote in message ... I have three Workbooks and all need to be open at once. The one required at any given time is selected using "Window" in the Menu Bar. Each Workbook has an associated and unique Custom Toolbar brought up when the Workbook is first opened, using "Auto_Open". To close each WB there is a button on its associated toolbar which operates a WB close module (macro). There are two things I cannot fathom how to do. 1. When I select a different WB (ie one of the other two via "Window") I need its associated unique toolbar to be displayed and that of the parting WB removed. 2. When I close a given WB, using its unique toolbar's close button, I need that toolbar to be removed and replaced by the one associated with the (next) WB now appearing. I can achieve 1 above by putting suitable code in ThisWorkbook, Workbook Activate. However when I do 2 above I get an error message "Application-defined or object-defined error" I can't find a suitable event kicked off by the closure of the "old" WB and the arrival of the "new" Any help or suggestions would be greatly appreciated. Don |
Eenie meenie minee......
Many thanks Jeff - that fixed it.
Don -----Original Message----- I think you need to trap application events. You can do this by adding a class module with the following in the declarations: Public WithEvents xlApp As Application You also need to declare this in a standard module: Public xlApplication As New clsAppEvents And this in the ThisWorkbook object: Private Sub Workbook_Open() Set xlApplication.xlApp = Application End Sub This will expose application events in the class module, one of which being xlApp_Workbook_Activate. You can then do whatever you wish based on the workbook that is open (select xlApp in the object dropdown at the the top of the VBE - the possible procedures are then listed in the procedure dropdown). Cheers, Jeff "don bowyer" wrote in message ... I have three Workbooks and all need to be open at once. The one required at any given time is selected using "Window" in the Menu Bar. Each Workbook has an associated and unique Custom Toolbar brought up when the Workbook is first opened, using "Auto_Open". To close each WB there is a button on its associated toolbar which operates a WB close module (macro). There are two things I cannot fathom how to do. 1. When I select a different WB (ie one of the other two via "Window") I need its associated unique toolbar to be displayed and that of the parting WB removed. 2. When I close a given WB, using its unique toolbar's close button, I need that toolbar to be removed and replaced by the one associated with the (next) WB now appearing. I can achieve 1 above by putting suitable code in ThisWorkbook, Workbook Activate. However when I do 2 above I get an error message "Application-defined or object-defined error" I can't find a suitable event kicked off by the closure of the "old" WB and the arrival of the "new" Any help or suggestions would be greatly appreciated. Don . |
All times are GMT +1. The time now is 03:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com