Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Workbook_Activate event
I'm using Excel 2003 under Win XP
I have several *.xls WorkBooks open under the same instance of the Excel Application. Each has a unique associated custom toolbar (CommandBar) which is made visible by the following code located in ThisWorkbook/ Microsoft Excel Objects Private Sub Workbook_Activate() Application.CommandBars("MyBook1").Visible = True End Sub In the same ThisWorkbook location is also the following code. Private Sub Workbook_Deactivate() Application.CommandBars("MyBook1").Visible = False End Sub In the simple case, with only two WorkBooks open and book#2, say, visible, if I select Window on the menu bar and select book#1, then book#1 becomes visible and so does its associated tool bar, while book#2 is no longer visible nor, importantly is its associated ToolBar. I can toggle between the 2 books and every time, the correct ToolBar for the visible book is visible. This operation is correct and as required. The problem comes when either book is closed. Under this situation, if say book#1 is closed using the following code:- Workbooks("MyBook1.xls").Close SaveChanges:=False I had expected this to trigger the "Private Sub Workbook_Activate()" event of the remaining workbook as it becomes visible, but it doesn't; the event for some reason, is not triggered. I need something which triggers when this remaining book appears, or in the more complex situation of several books open and one being closed, something which triggers when the next book "in the line-up" becomes visible. Any suggestions gratefully accepted. donwb |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Workbook_Activate event
Rather than use Application use Thisworkbook. Thisworkbook will ALWAYS refer
to the wrorkbook with where the Macro is running and won't have problems if you open other workbooks. When you use more than one workbook always use thisworkbook and set a seperate variables to the other workbooks to minimize errors. The problem with excel is when you open a workbook or add worksheets the focus changes and frequently causes errors. ThisWorkbook.CommandBars("MyBook1").Visible = False "donwb" wrote: I'm using Excel 2003 under Win XP I have several *.xls WorkBooks open under the same instance of the Excel Application. Each has a unique associated custom toolbar (CommandBar) which is made visible by the following code located in ThisWorkbook/ Microsoft Excel Objects Private Sub Workbook_Activate() Application.CommandBars("MyBook1").Visible = True End Sub In the same ThisWorkbook location is also the following code. Private Sub Workbook_Deactivate() Application.CommandBars("MyBook1").Visible = False End Sub In the simple case, with only two WorkBooks open and book#2, say, visible, if I select Window on the menu bar and select book#1, then book#1 becomes visible and so does its associated tool bar, while book#2 is no longer visible nor, importantly is its associated ToolBar. I can toggle between the 2 books and every time, the correct ToolBar for the visible book is visible. This operation is correct and as required. The problem comes when either book is closed. Under this situation, if say book#1 is closed using the following code:- Workbooks("MyBook1.xls").Close SaveChanges:=False I had expected this to trigger the "Private Sub Workbook_Activate()" event of the remaining workbook as it becomes visible, but it doesn't; the event for some reason, is not triggered. I need something which triggers when this remaining book appears, or in the more complex situation of several books open and one being closed, something which triggers when the next book "in the line-up" becomes visible. Any suggestions gratefully accepted. donwb |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Workbook_Activate event
Hi Joel
Many thanks for your input. Yes I see the logic of your suggestion. I replaced Application with ThisWorkBook, but get an error message "object variable or With Block variable not set." Should the revised code still be located in Microsoft Excel Objects / ThisWorkbook, or should it now go in a separate module under Modules? donwb "Joel" wrote in message ... Rather than use Application use Thisworkbook. Thisworkbook will ALWAYS refer to the wrorkbook with where the Macro is running and won't have problems if you open other workbooks. When you use more than one workbook always use thisworkbook and set a seperate variables to the other workbooks to minimize errors. The problem with excel is when you open a workbook or add worksheets the focus changes and frequently causes errors. ThisWorkbook.CommandBars("MyBook1").Visible = False "donwb" wrote: I'm using Excel 2003 under Win XP I have several *.xls WorkBooks open under the same instance of the Excel Application. Each has a unique associated custom toolbar (CommandBar) which is made visible by the following code located in ThisWorkbook/ Microsoft Excel Objects Private Sub Workbook_Activate() Application.CommandBars("MyBook1").Visible = True End Sub In the same ThisWorkbook location is also the following code. Private Sub Workbook_Deactivate() Application.CommandBars("MyBook1").Visible = False End Sub In the simple case, with only two WorkBooks open and book#2, say, visible, if I select Window on the menu bar and select book#1, then book#1 becomes visible and so does its associated tool bar, while book#2 is no longer visible nor, importantly is its associated ToolBar. I can toggle between the 2 books and every time, the correct ToolBar for the visible book is visible. This operation is correct and as required. The problem comes when either book is closed. Under this situation, if say book#1 is closed using the following code:- Workbooks("MyBook1.xls").Close SaveChanges:=False I had expected this to trigger the "Private Sub Workbook_Activate()" event of the remaining workbook as it becomes visible, but it doesn't; the event for some reason, is not triggered. I need something which triggers when this remaining book appears, or in the more complex situation of several books open and one being closed, something which triggers when the next book "in the line-up" becomes visible. Any suggestions gratefully accepted. donwb |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Workbook_Activate event
I'm alwaays learning new things. I added commands bar manually from the
spreadsheet, but not through a program. the toolbars and menues are not associated with a workbook, they are part of the excel application. Your toolbar commands don't need the word application. commands working with workbooks such SAVEAS can use Thisworkbook. "donwb" wrote: Hi Joel Many thanks for your input. Yes I see the logic of your suggestion. I replaced Application with ThisWorkBook, but get an error message "object variable or With Block variable not set." Should the revised code still be located in Microsoft Excel Objects / ThisWorkbook, or should it now go in a separate module under Modules? donwb "Joel" wrote in message ... Rather than use Application use Thisworkbook. Thisworkbook will ALWAYS refer to the wrorkbook with where the Macro is running and won't have problems if you open other workbooks. When you use more than one workbook always use thisworkbook and set a seperate variables to the other workbooks to minimize errors. The problem with excel is when you open a workbook or add worksheets the focus changes and frequently causes errors. ThisWorkbook.CommandBars("MyBook1").Visible = False "donwb" wrote: I'm using Excel 2003 under Win XP I have several *.xls WorkBooks open under the same instance of the Excel Application. Each has a unique associated custom toolbar (CommandBar) which is made visible by the following code located in ThisWorkbook/ Microsoft Excel Objects Private Sub Workbook_Activate() Application.CommandBars("MyBook1").Visible = True End Sub In the same ThisWorkbook location is also the following code. Private Sub Workbook_Deactivate() Application.CommandBars("MyBook1").Visible = False End Sub In the simple case, with only two WorkBooks open and book#2, say, visible, if I select Window on the menu bar and select book#1, then book#1 becomes visible and so does its associated tool bar, while book#2 is no longer visible nor, importantly is its associated ToolBar. I can toggle between the 2 books and every time, the correct ToolBar for the visible book is visible. This operation is correct and as required. The problem comes when either book is closed. Under this situation, if say book#1 is closed using the following code:- Workbooks("MyBook1.xls").Close SaveChanges:=False I had expected this to trigger the "Private Sub Workbook_Activate()" event of the remaining workbook as it becomes visible, but it doesn't; the event for some reason, is not triggered. I need something which triggers when this remaining book appears, or in the more complex situation of several books open and one being closed, something which triggers when the next book "in the line-up" becomes visible. Any suggestions gratefully accepted. donwb |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook_Activate not triggered by Alt+Tab | Excel Programming | |||
Manually call Workbook_Activate procedure | Excel Programming | |||
Lost clipboard contents on Workbook_Activate | Excel Programming | |||
Workbook_Activate question | Excel Programming |