Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Hiding all toolbars
Hi,
Is there a way to hide all toolbars in a particular workbook, that will not effect any other excel files, even if they are open at the same time? If possible, I want to hide absolutely everything, including the menu bar. Cheers, |
#2
|
|||
|
|||
You could use the workbook activate and deactivate events.
First add a new worksheet called TB. Then paste the following code into the workbook code sheet - right click on the Excel Logo to the left of File menu and select view code to get to the workbook code sheet. The code keeps track of which toolbars were displayed so that these can be restored when you deactivate the book. You can't hide the Menu Bar completely (I don't think) but you can delete all the menu items off it. Hope this helps Rowan Private Sub Workbook_Activate() Dim tbSheet As Worksheet Dim tbCount As Integer Dim tb As CommandBar Dim ctr As CommandBarPopup Set tbSheet = Sheets("TB") tbSheet.Range("A:A").ClearContents tbSheet.Visible = xlSheetHidden tbCount = 0 For Each tb In Application.CommandBars If tb.Type = msoBarTypeNormal Then If tb.Visible Then tbCount = tbCount + 1 tbSheet.Cells(tbCount, 1).Value = tb.Name tb.Visible = False End If End If Next tb For Each ctr In Application.CommandBars(1).Controls ctr.Delete Next ctr Application.DisplayStatusBar = False Application.DisplayFormulaBar = False End Sub Private Sub Workbook_Deactivate() Dim tbCount As Integer Dim tb As String Dim tbSheet As Worksheet Set tbSheet = Sheets("TB") tbCount = 1 tb = tbSheet.Cells(tbCount, 1).Value Do While tb < "" Application.CommandBars(tb).Visible = True tbCount = tbCount + 1 tb = tbSheet.Cells(tbCount, 1).Value Loop Application.CommandBars(1).Reset Application.DisplayStatusBar = True Application.DisplayFormulaBar = True End Sub "Iain" wrote: Hi, Is there a way to hide all toolbars in a particular workbook, that will not effect any other excel files, even if they are open at the same time? If possible, I want to hide absolutely everything, including the menu bar. Cheers, |
#3
|
|||
|
|||
Rowan
I have copied your code and it works. However it also places the Formula bar and the status bar when I open up my next workbook - both of which are never visible when I exit excel. so either your code is memerisong something which isn't there or is placing the toolbars on exiting. Any ideas how to stop this ? Mike "Rowan" wrote: You could use the workbook activate and deactivate events. First add a new worksheet called TB. Then paste the following code into the workbook code sheet - right click on the Excel Logo to the left of File menu and select view code to get to the workbook code sheet. The code keeps track of which toolbars were displayed so that these can be restored when you deactivate the book. You can't hide the Menu Bar completely (I don't think) but you can delete all the menu items off it. Hope this helps Rowan Private Sub Workbook_Activate() Dim tbSheet As Worksheet Dim tbCount As Integer Dim tb As CommandBar Dim ctr As CommandBarPopup Set tbSheet = Sheets("TB") tbSheet.Range("A:A").ClearContents tbSheet.Visible = xlSheetHidden tbCount = 0 For Each tb In Application.CommandBars If tb.Type = msoBarTypeNormal Then If tb.Visible Then tbCount = tbCount + 1 tbSheet.Cells(tbCount, 1).Value = tb.Name tb.Visible = False End If End If Next tb For Each ctr In Application.CommandBars(1).Controls ctr.Delete Next ctr Application.DisplayStatusBar = False Application.DisplayFormulaBar = False End Sub Private Sub Workbook_Deactivate() Dim tbCount As Integer Dim tb As String Dim tbSheet As Worksheet Set tbSheet = Sheets("TB") tbCount = 1 tb = tbSheet.Cells(tbCount, 1).Value Do While tb < "" Application.CommandBars(tb).Visible = True tbCount = tbCount + 1 tb = tbSheet.Cells(tbCount, 1).Value Loop Application.CommandBars(1).Reset Application.DisplayStatusBar = True Application.DisplayFormulaBar = True End Sub "Iain" wrote: Hi, Is there a way to hide all toolbars in a particular workbook, that will not effect any other excel files, even if they are open at the same time? If possible, I want to hide absolutely everything, including the menu bar. Cheers, |
#4
|
|||
|
|||
Dim sStatusBar
Dim sFormulaBar Private Sub Workbook_Activate() Dim tbSheet As Worksheet Dim tbCount As Integer Dim tb As CommandBar Dim ctr As CommandBarPopup Set tbSheet = Sheets("TB") tbSheet.Range("A:A").ClearContents tbSheet.Visible = xlSheetHidden tbCount = 0 For Each tb In Application.CommandBars If tb.Type = msoBarTypeNormal Then If tb.Visible Then tbCount = tbCount + 1 tbSheet.Cells(tbCount, 1).Value = tb.Name tb.Visible = False End If End If Next tb For Each ctr In Application.CommandBars(1).Controls ctr.Delete Next ctr sStatusBar = Application.DisplayStatusBar sFormulaBar = Application.DisplayFormulaBar Application.DisplayStatusBar = False Application.DisplayFormulaBar = False End Sub Private Sub Workbook_Deactivate() Dim tbCount As Integer Dim tb As String Dim tbSheet As Worksheet Set tbSheet = Sheets("TB") tbCount = 1 tb = tbSheet.Cells(tbCount, 1).Value Do While tb < "" Application.CommandBars(tb).Visible = True tbCount = tbCount + 1 tb = tbSheet.Cells(tbCount, 1).Value Loop Application.CommandBars(1).Reset Application.DisplayStatusBar = sStatusBar Application.DisplayFormulaBar = sFormulaBar End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Mike" wrote in message ... Rowan I have copied your code and it works. However it also places the Formula bar and the status bar when I open up my next workbook - both of which are never visible when I exit excel. so either your code is memerisong something which isn't there or is placing the toolbars on exiting. Any ideas how to stop this ? Mike "Rowan" wrote: You could use the workbook activate and deactivate events. First add a new worksheet called TB. Then paste the following code into the workbook code sheet - right click on the Excel Logo to the left of File menu and select view code to get to the workbook code sheet. The code keeps track of which toolbars were displayed so that these can be restored when you deactivate the book. You can't hide the Menu Bar completely (I don't think) but you can delete all the menu items off it. Hope this helps Rowan Private Sub Workbook_Activate() Dim tbSheet As Worksheet Dim tbCount As Integer Dim tb As CommandBar Dim ctr As CommandBarPopup Set tbSheet = Sheets("TB") tbSheet.Range("A:A").ClearContents tbSheet.Visible = xlSheetHidden tbCount = 0 For Each tb In Application.CommandBars If tb.Type = msoBarTypeNormal Then If tb.Visible Then tbCount = tbCount + 1 tbSheet.Cells(tbCount, 1).Value = tb.Name tb.Visible = False End If End If Next tb For Each ctr In Application.CommandBars(1).Controls ctr.Delete Next ctr Application.DisplayStatusBar = False Application.DisplayFormulaBar = False End Sub Private Sub Workbook_Deactivate() Dim tbCount As Integer Dim tb As String Dim tbSheet As Worksheet Set tbSheet = Sheets("TB") tbCount = 1 tb = tbSheet.Cells(tbCount, 1).Value Do While tb < "" Application.CommandBars(tb).Visible = True tbCount = tbCount + 1 tb = tbSheet.Cells(tbCount, 1).Value Loop Application.CommandBars(1).Reset Application.DisplayStatusBar = True Application.DisplayFormulaBar = True End Sub "Iain" wrote: Hi, Is there a way to hide all toolbars in a particular workbook, that will not effect any other excel files, even if they are open at the same time? If possible, I want to hide absolutely everything, including the menu bar. Cheers, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to reset Excel 2000 toolbars to "factory defaults"? | Excel Discussion (Misc queries) | |||
Saving Customized Toolbars in Excel 2003 | Excel Discussion (Misc queries) | |||
Customizing ToolBars | Excel Discussion (Misc queries) | |||
Stubborn toolbars in Excel | Excel Discussion (Misc queries) | |||
Cant add an icon to toolbars | Excel Discussion (Misc queries) |