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

*** Sent via Developersdex http://www.developersdex.com ***

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Darin Kramer" <(E-Mail Removed)> wrote in message
news:%23sEi%(E-Mail Removed)...
>
>
> 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
>
> *** Sent via Developersdex http://www.developersdex.com ***

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" wrote:

>
>
> 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
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

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


*** Sent via Developersdex http://www.developersdex.com ***

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)


"Darin Kramer" wrote:

> 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
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

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)

"Darin Kramer" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
>
> *** Sent via Developersdex http://www.developersdex.com ***