Command Bar - make it go away when you change sheets

[Darin Kramer]

Hi There,

I have some VB that creates a Command bar (toolbar) and populates it
with buttons which I assign macros too.

I would like to have the toolbar close when any other sheet is selected
(It is launched from say sheet x, and if you click sheet y I want the
toolbar to dissapear (well close!)

Regards

Darin

 

[Jon Peltier]

Add a few event procedures to the ThisWorkbook module of the workbook that
launches the toolbar:

Private Sub Workbook_Activate()
CommandBars("My Toolbar").Visible = True
End Sub

Private Sub Workbook_Deactivate()
CommandBars("My Toolbar").Visible = False
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
CommandBars("My Toolbar").Visible = (Sh.Name = MySheet.Name)
End Sub

- Jon

 

[Guest]

Darin,

In the Sheet x code module, you need something like this:

Private Sub Worksheet_Activate()
Application.CommandBars("My Command Bar").Visible = True
End Sub
Private Sub Worksheet_Deactivate()
Application.CommandBars("My Command Bar").Visible = False
End Sub

 

[Darin Kramer]

Hi Jon,

Almost works - just struggling with syntax of last line...

CommandBars("Functionality_APP_D").Visible = (Sh.Name = APP_D) What Im
doing wrong...?(if my sheet is APP_D)

Regards

D

 

[Guest]

Is APP_D your sheet name or sheet code name?

If it's the sheet name, it should be like this:

CommandBars("Functionality_APP_D").Visible = (Sh.Name = "APP_D")

If it's the code name, it should be like this:

CommandBars("Functionality_APP_D").Visible = (Sh.Name = APP_D.Name)

 

[Chip Pearson]

If the sheet is named "APP_D", then you need to change

CommandBars("Functionality_APP_D").Visible = (Sh.Name = APP_D)
to
CommandBars("Functionality_APP_D").Visible = (Sh.Name = "APP_D")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)