Programmatically Disabling Excel Toolbars for one session only?

[BW]

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?

 

[Bob Phillips]

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)

 

[Peter Rooney]

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

 

[BW]

Thanks gentlemen.. It worked..