Deactivate workbook commandbar for one sheet?

[mitchellwma1]

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. D

 

[acampbell012]

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

 

[Bob Phillips]

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)

 

[mitchellwma1]

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!