| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
close (hide) toolbars
Hi
Can anybody advise how I can close or hide the 'Formatting', 'Standard', 'Formula', 'Status' and the 'Worksheet Menubar' toolbars when my excel worksheet is opened, but NOT affect their apperance in any other excel worksheet. I would imagine it takes some VBA script, but any ideas for a novice ?? thanks |
|
#2
|
|||
|
|||
|
Hi Anthony
You can use this events in the thisworkbook module to run your code Private Sub Workbook_Activate() End Sub Private Sub Workbook_Deactivate() End Sub For more information see http://www.rondebruin.com/menuid.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Anthony" wrote in message ... Hi Can anybody advise how I can close or hide the 'Formatting', 'Standard', 'Formula', 'Status' and the 'Worksheet Menubar' toolbars when my excel worksheet is opened, but NOT affect their apperance in any other excel worksheet. I would imagine it takes some VBA script, but any ideas for a novice ?? thanks |
|
#3
|
|||
|
|||
|
sorry Ron but I'm a bit of a novice when it comes to VB.
I have opened the VB code and pasted urs (from ur orig reply) into the thisworkbook module, but it doesn't seem to do much ! if I open my excel workshhet again , all the toolbars I want to hide are still there ! HELP PLEASE !! "Ron de Bruin" wrote: Hi Anthony You can use this events in the thisworkbook module to run your code Private Sub Workbook_Activate() End Sub Private Sub Workbook_Deactivate() End Sub For more information see http://www.rondebruin.com/menuid.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Anthony" wrote in message ... Hi Can anybody advise how I can close or hide the 'Formatting', 'Standard', 'Formula', 'Status' and the 'Worksheet Menubar' toolbars when my excel worksheet is opened, but NOT affect their apperance in any other excel worksheet. I would imagine it takes some VBA script, but any ideas for a novice ?? thanks |
|
#4
|
|||
|
|||
|
Show me the code you copy in the two events
You can something like this Private Sub Workbook_Activate() ' This will disable all Command bars except ' the Worksheet Menu Bar Dim Cbar As CommandBar For Each Cbar In Application.CommandBars If Cbar.Name < "Worksheet Menu Bar" Then Cbar.Enabled = False End If Next With Application .DisplayFormulaBar = False .DisplayStatusBar = False End With End Sub Private Sub Workbook_Deactivate() Dim Cbar As CommandBar For Each Cbar In Application.CommandBars If Cbar.Name < "Worksheet Menu Bar" Then Cbar.Enabled = True End If Next With Application .DisplayFormulaBar = True .DisplayStatusBar = True End With End Sub Fore more information about events see http://www.cpearson.com/excel/events.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Anthony" wrote in message ... sorry Ron but I'm a bit of a novice when it comes to VB. I have opened the VB code and pasted urs (from ur orig reply) into the thisworkbook module, but it doesn't seem to do much ! if I open my excel workshhet again , all the toolbars I want to hide are still there ! HELP PLEASE !! "Ron de Bruin" wrote: Hi Anthony You can use this events in the thisworkbook module to run your code Private Sub Workbook_Activate() End Sub Private Sub Workbook_Deactivate() End Sub For more information see http://www.rondebruin.com/menuid.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Anthony" wrote in message ... Hi Can anybody advise how I can close or hide the 'Formatting', 'Standard', 'Formula', 'Status' and the 'Worksheet Menubar' toolbars when my excel worksheet is opened, but NOT affect their apperance in any other excel worksheet. I would imagine it takes some VBA script, but any ideas for a novice ?? thanks |
|
#5
|
|||
|
|||
|
Ron
Fantastic - it worked , however just one thing# If I have a personal toolbar names 'log' can/how do I change ur code to show just this toolbar on opening? thanks again "Ron de Bruin" wrote: Show me the code you copy in the two events You can something like this Private Sub Workbook_Activate() ' This will disable all Command bars except ' the Worksheet Menu Bar Dim Cbar As CommandBar For Each Cbar In Application.CommandBars If Cbar.Name < "Worksheet Menu Bar" Then Cbar.Enabled = False End If Next With Application .DisplayFormulaBar = False .DisplayStatusBar = False End With End Sub Private Sub Workbook_Deactivate() Dim Cbar As CommandBar For Each Cbar In Application.CommandBars If Cbar.Name < "Worksheet Menu Bar" Then Cbar.Enabled = True End If Next With Application .DisplayFormulaBar = True .DisplayStatusBar = True End With End Sub Fore more information about events see http://www.cpearson.com/excel/events.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Anthony" wrote in message ... sorry Ron but I'm a bit of a novice when it comes to VB. I have opened the VB code and pasted urs (from ur orig reply) into the thisworkbook module, but it doesn't seem to do much ! if I open my excel workshhet again , all the toolbars I want to hide are still there ! HELP PLEASE !! "Ron de Bruin" wrote: Hi Anthony You can use this events in the thisworkbook module to run your code Private Sub Workbook_Activate() End Sub Private Sub Workbook_Deactivate() End Sub For more information see http://www.rondebruin.com/menuid.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Anthony" wrote in message ... Hi Can anybody advise how I can close or hide the 'Formatting', 'Standard', 'Formula', 'Status' and the 'Worksheet Menubar' toolbars when my excel worksheet is opened, but NOT affect their apperance in any other excel worksheet. I would imagine it takes some VBA script, but any ideas for a novice ?? thanks |
|
#6
|
|||
|
|||
|
Try this
Sub Disable_Command_Bars_1() 'This will disable all BuiltIn Command bars Dim Cbar As CommandBar For Each Cbar In Application.CommandBars If Cbar.BuiltIn = True Then Cbar.Enabled = False End If Next End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Anthony" wrote in message ... Ron Fantastic - it worked , however just one thing# If I have a personal toolbar names 'log' can/how do I change ur code to show just this toolbar on opening? thanks again "Ron de Bruin" wrote: Show me the code you copy in the two events You can something like this Private Sub Workbook_Activate() ' This will disable all Command bars except ' the Worksheet Menu Bar Dim Cbar As CommandBar For Each Cbar In Application.CommandBars If Cbar.Name < "Worksheet Menu Bar" Then Cbar.Enabled = False End If Next With Application .DisplayFormulaBar = False .DisplayStatusBar = False End With End Sub Private Sub Workbook_Deactivate() Dim Cbar As CommandBar For Each Cbar In Application.CommandBars If Cbar.Name < "Worksheet Menu Bar" Then Cbar.Enabled = True End If Next With Application .DisplayFormulaBar = True .DisplayStatusBar = True End With End Sub Fore more information about events see http://www.cpearson.com/excel/events.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Anthony" wrote in message ... sorry Ron but I'm a bit of a novice when it comes to VB. I have opened the VB code and pasted urs (from ur orig reply) into the thisworkbook module, but it doesn't seem to do much ! if I open my excel workshhet again , all the toolbars I want to hide are still there ! HELP PLEASE !! "Ron de Bruin" wrote: Hi Anthony You can use this events in the thisworkbook module to run your code Private Sub Workbook_Activate() End Sub Private Sub Workbook_Deactivate() End Sub For more information see http://www.rondebruin.com/menuid.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Anthony" wrote in message ... Hi Can anybody advise how I can close or hide the 'Formatting', 'Standard', 'Formula', 'Status' and the 'Worksheet Menubar' toolbars when my excel worksheet is opened, but NOT affect their apperance in any other excel worksheet. I would imagine it takes some VBA script, but any ideas for a novice ?? thanks |
|
#7
|
|||
|
|||
|
Ron,
So do I just use the code given in ur last reply to show only my toolbar ?? thanks again Anthony "Ron de Bruin" wrote: Try this Sub Disable_Command_Bars_1() 'This will disable all BuiltIn Command bars Dim Cbar As CommandBar For Each Cbar In Application.CommandBars If Cbar.BuiltIn = True Then Cbar.Enabled = False End If Next End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Anthony" wrote in message ... Ron Fantastic - it worked , however just one thing# If I have a personal toolbar names 'log' can/how do I change ur code to show just this toolbar on opening? thanks again "Ron de Bruin" wrote: Show me the code you copy in the two events You can something like this Private Sub Workbook_Activate() ' This will disable all Command bars except ' the Worksheet Menu Bar Dim Cbar As CommandBar For Each Cbar In Application.CommandBars If Cbar.Name < "Worksheet Menu Bar" Then Cbar.Enabled = False End If Next With Application .DisplayFormulaBar = False .DisplayStatusBar = False End With End Sub Private Sub Workbook_Deactivate() Dim Cbar As CommandBar For Each Cbar In Application.CommandBars If Cbar.Name < "Worksheet Menu Bar" Then Cbar.Enabled = True End If Next With Application .DisplayFormulaBar = True .DisplayStatusBar = True End With End Sub Fore more information about events see http://www.cpearson.com/excel/events.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Anthony" wrote in message ... sorry Ron but I'm a bit of a novice when it comes to VB. I have opened the VB code and pasted urs (from ur orig reply) into the thisworkbook module, but it doesn't seem to do much ! if I open my excel workshhet again , all the toolbars I want to hide are still there ! HELP PLEASE !! "Ron de Bruin" wrote: Hi Anthony You can use this events in the thisworkbook module to run your code Private Sub Workbook_Activate() End Sub Private Sub Workbook_Deactivate() End Sub For more information see http://www.rondebruin.com/menuid.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Anthony" wrote in message ... Hi Can anybody advise how I can close or hide the 'Formatting', 'Standard', 'Formula', 'Status' and the 'Worksheet Menubar' toolbars when my excel worksheet is opened, but NOT affect their apperance in any other excel worksheet. I would imagine it takes some VBA script, but any ideas for a novice ?? thanks |
|
#8
|
|||
|
|||
|
Hi Anthony
Use this working example Change YourCustomBar to your bar name Private Sub Workbook_Activate() Dim Cbar As CommandBar For Each Cbar In Application.CommandBars If Cbar.Name < "Worksheet Menu Bar" And Cbar.Name < "YourCustomBar" Then Cbar.Enabled = False End If Next With Application .DisplayFormulaBar = False .DisplayStatusBar = False End With End Sub Private Sub Workbook_Deactivate() Dim Cbar As CommandBar For Each Cbar In Application.CommandBars Cbar.Enabled = True Next With Application .DisplayFormulaBar = True .DisplayStatusBar = True End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Anthony" wrote in message ... Ron, So do I just use the code given in ur last reply to show only my toolbar ?? thanks again Anthony "Ron de Bruin" wrote: Try this Sub Disable_Command_Bars_1() 'This will disable all BuiltIn Command bars Dim Cbar As CommandBar For Each Cbar In Application.CommandBars If Cbar.BuiltIn = True Then Cbar.Enabled = False End If Next End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Anthony" wrote in message ... Ron Fantastic - it worked , however just one thing# If I have a personal toolbar names 'log' can/how do I change ur code to show just this toolbar on opening? thanks again "Ron de Bruin" wrote: Show me the code you copy in the two events You can something like this Private Sub Workbook_Activate() ' This will disable all Command bars except ' the Worksheet Menu Bar Dim Cbar As CommandBar For Each Cbar In Application.CommandBars If Cbar.Name < "Worksheet Menu Bar" Then Cbar.Enabled = False End If Next With Application .DisplayFormulaBar = False .DisplayStatusBar = False End With End Sub Private Sub Workbook_Deactivate() Dim Cbar As CommandBar For Each Cbar In Application.CommandBars If Cbar.Name < "Worksheet Menu Bar" Then Cbar.Enabled = True End If Next With Application .DisplayFormulaBar = True .DisplayStatusBar = True End With End Sub Fore more information about events see http://www.cpearson.com/excel/events.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Anthony" wrote in message ... sorry Ron but I'm a bit of a novice when it comes to VB. I have opened the VB code and pasted urs (from ur orig reply) into the thisworkbook module, but it doesn't seem to do much ! if I open my excel workshhet again , all the toolbars I want to hide are still there ! HELP PLEASE !! "Ron de Bruin" wrote: Hi Anthony You can use this events in the thisworkbook module to run your code Private Sub Workbook_Activate() End Sub Private Sub Workbook_Deactivate() End Sub For more information see http://www.rondebruin.com/menuid.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Anthony" wrote in message ... Hi Can anybody advise how I can close or hide the 'Formatting', 'Standard', 'Formula', 'Status' and the 'Worksheet Menubar' toolbars when my excel worksheet is opened, but NOT affect their apperance in any other excel worksheet. I would imagine it takes some VBA script, but any ideas for a novice ?? thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I hide and show all toolbars | Excel Worksheet Functions | |||
| Customizing ToolBars | Excel Discussion (Misc queries) | |||
| Stubborn toolbars in Excel | Excel Discussion (Misc queries) | |||
| How can I close only 1 workbook when I have many open? | Excel Discussion (Misc queries) | |||
| Conditional Hide function for Excel | Excel Worksheet Functions | |||
Linear Mode
