|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 11:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com