Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Bar - make it go away when you change sheets
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Bar - make it go away when you change sheets
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" wrote in message ... 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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Bar - make it go away when you change sheets
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 *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Bar - make it go away when you change sheets
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 *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Bar - make it go away when you change sheets
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 *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Bar - make it go away when you change sheets
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" wrote in message ... 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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Select All Sheets and Make Change to Footer | Excel Discussion (Misc queries) | |||
can you make an IF command run a macro??? | Excel Programming | |||
How to make command buttons move among worksheets? | Excel Programming | |||
How do I make a command button invisible or visible? | Excel Programming | |||
Make Command Button visible only in .xlt? | Excel Programming |