Programmatically Disabling Excel Toolbars for one session only
Hi, BW
You also need to take care that you don't disable the Excel MenuBar when
you're disabling the Toolbars. One nice way that someone showed me how to do
it was:
Private Sub Workbook_Open()
Application.DisplayFullScreen = True 'Put this commands before
UserToolBars(xlon)/(xloff)
'otherwise the toolbar generated
by turning full screen on/off
'causes a conflict.
UserToolBars (xlOn) 'set toolbar state ON & turn toolbars OFF
End Sub
Private Sub Workbook_BeforeClose(cancel As Boolean)
Application.DisplayFullScreen = False
UserToolBars (xlOff) 'set toolbar state OFF & turn toolbars ON
End Sub
Sub UserToolBars(State)
Static UserToolBars As New Collection
Dim UserBar
If State = xlOn Then
For Each UserBar In Application.CommandBars
If UserBar.Type < 1 And UserBar.Visible Then
UserToolBars.Add UserBar
UserBar.Visible = False
End If
Next UserBar
Else
For Each UserBar In UserToolBars
UserBar.Visible = True
Next UserBar
End If
End Sub
In this case, the first two macros go in your ThisWorkbook code page, the
third can go in a normal code module.
Hope this helps
Pete
"Bob Phillips" wrote:
That is generally the approach, but you need to reset on exit. Assuming you
have this in a particular workbook, put the code in the Workbook_Open event,
and reset in the BeforeClose event.
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB
End Sub
Private Sub Workbook_Open()
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = False
Next oCB
End Sub
'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code
--
HTH
RP
(remove nothere from the email address if mailing direct)
"BW" wrote in message
...
Hello,
How can i programmatically disable (not display) the excel toolbars for a
single instance of excel such that the original toolbars configuration is
reappears on subsequent invocations of excel.
ie.
For Each bar In Application.CommandBars
If bar.Visible Then bar.Visible=False
Next
Problem is when i do the above, all subsequent invocations of excel will
have no toolbars displayed. Only way i can think of reverting back to
original toolbars configuration is to save which toolbars are visible, and
then set them back to true when the workbook closes but this seems very
clumsy.
is there a more efficient approach?
|