Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deactivate workbook commandbar for one sheet?
I've never run into this one before. I usu. need a repeating toolbar
for any sheet in a workbook. Therefore, in the "ThisWorkbook" I copy this code into each new workbook I make that uses a custom commandbar: Private Sub Workbook_Activate() On Error Resume Next Application.CommandBars("My Assigned Toolbar Name").Visible = True End Sub Private Sub Workbook_Deactivate() ' CommandBars disappear (whilst the workbook remains open, but another is in view) On Error Resume Next Application.CommandBars("My Assigned Toolbar Name").Visible = False End Sub So that takes care of activating the custom toolbar on workbook open, and deactivating it on workbook close and the toolbar shows up on any sheet I create within that workbook. But, what does one do if one wants to "hide" the toolbar for sheet 2, say? Thanks. :oD |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deactivate workbook commandbar for one sheet?
In the sheet module of the sheet you want to hide the menu:
Private Sub Worksheet_Activate() Application.CommandBars("My Assigned Toolbar Name").Visible = False End Sub To redisplay the menu when selecting another sheet, place this in the same module: Private Sub Worksheet_Deactivate() Application.CommandBars("My Assigned Toolbar Name").Visible = True End Sub wrote: I've never run into this one before. I usu. need a repeating toolbar for any sheet in a workbook. Therefore, in the "ThisWorkbook" I copy this code into each new workbook I make that uses a custom commandbar: Private Sub Workbook_Activate() On Error Resume Next Application.CommandBars("My Assigned Toolbar Name").Visible = True End Sub Private Sub Workbook_Deactivate() ' CommandBars disappear (whilst the workbook remains open, but another is in view) On Error Resume Next Application.CommandBars("My Assigned Toolbar Name").Visible = False End Sub So that takes care of activating the custom toolbar on workbook open, and deactivating it on workbook close and the toolbar shows up on any sheet I create within that workbook. But, what does one do if one wants to "hide" the toolbar for sheet 2, say? Thanks. :oD |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deactivate workbook commandbar for one sheet?
Put the hide code in the worksheet activate code for that worksheet, and the
show code in the deactivate event. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I've never run into this one before. I usu. need a repeating toolbar for any sheet in a workbook. Therefore, in the "ThisWorkbook" I copy this code into each new workbook I make that uses a custom commandbar: Private Sub Workbook_Activate() On Error Resume Next Application.CommandBars("My Assigned Toolbar Name").Visible = True End Sub Private Sub Workbook_Deactivate() ' CommandBars disappear (whilst the workbook remains open, but another is in view) On Error Resume Next Application.CommandBars("My Assigned Toolbar Name").Visible = False End Sub So that takes care of activating the custom toolbar on workbook open, and deactivating it on workbook close and the toolbar shows up on any sheet I create within that workbook. But, what does one do if one wants to "hide" the toolbar for sheet 2, say? Thanks. :oD |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deactivate workbook commandbar for one sheet?
Well, that is just so neat. That worked great. I just put the
opposite to what is in the workbook module into the sheet module, as per the above. i.e., where it says for the toolbar to show (true) in the workbook, I hide it in the worksheet (false). That's too neat for words. It was hoping it would be that simple. thx! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Command Bar & Workbook Deactivate | Excel Programming | |||
Deactivate sheet | Excel Programming | |||
Workbook Deactivate | Excel Programming | |||
Workbook Deactivate Event | Excel Programming | |||
UserForms to deactivate workbook | Excel Programming |