Re: 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 work***
> '(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" <BW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:78AFA651-0B3A-41A5-856C-59562E1267CE@xxxxxxxxxxxxxxxx
> > 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?
> >
>
>
>
.