I manage a workbook that is used by lots of people, and therefore the
nuts and bolts are protected and locked. I would also like to hide all
the toolbars when this book opens. I understand that the user can
simply go to View--Toolbars to see them, but most of these people
won't have any reason to do that and I'd like to clean this interface
up as much as possible.

I've seen this done before, but I can't find the option(s) to make it
happen.

Thanks.

Michael

As you are saying "I understand that the user can simply go to
View--Toolbars", I am presuming you want 'Full screen view' If not you will
need to explicitly hide all the commandbars in the window. The former could
be done in the Workbook_Open() event. (To implement this right click on the
Excel Icon at the top left of the window and select view code... and paste
in here.

Private Sub Workbook_Open()
Application.DisplayFullScreen=True
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"michaelberrier" wrote in message
ups.com...
I manage a workbook that is used by lots of people, and therefore the
nuts and bolts are protected and locked. I would also like to hide all
the toolbars when this book opens. I understand that the user can
simply go to View--Toolbars to see them, but most of these people
won't have any reason to do that and I'd like to clean this interface
up as much as possible.

I've seen this done before, but I can't find the option(s) to make it
happen.

Thanks.

The full screen looks like a good idea, but it applies that attribute
to any workbook I open after that. Is there a way to turn that on for
only that workbook? I tried a Workbook_Close sub but that did nothing.

thanks
Nick Hodge wrote:
Michael

As you are saying "I understand that the user can simply go to
View--Toolbars", I am presuming you want 'Full screen view' If not you will
need to explicitly hide all the commandbars in the window. The former could
be done in the Workbook_Open() event. (To implement this right click on the
Excel Icon at the top left of the window and select view code... and paste
in here.

Private Sub Workbook_Open()
Application.DisplayFullScreen=True
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"michaelberrier" wrote in message
ups.com...
I manage a workbook that is used by lots of people, and therefore the
nuts and bolts are protected and locked. I would also like to hide all
the toolbars when this book opens. I understand that the user can
simply go to View--Toolbars to see them, but most of these people
won't have any reason to do that and I'd like to clean this interface
up as much as possible.

I've seen this done before, but I can't find the option(s) to make it
happen.

Thanks.

Michael

Try this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayFullScreen = False
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"michaelberrier" wrote in message
oups.com...
The full screen looks like a good idea, but it applies that attribute
to any workbook I open after that. Is there a way to turn that on for
only that workbook? I tried a Workbook_Close sub but that did nothing.

thanks
Nick Hodge wrote:
Michael

As you are saying "I understand that the user can simply go to
View--Toolbars", I am presuming you want 'Full screen view' If not you
will
need to explicitly hide all the commandbars in the window. The former
could
be done in the Workbook_Open() event. (To implement this right click on
the
Excel Icon at the top left of the window and select view code... and
paste
in here.

Private Sub Workbook_Open()
Application.DisplayFullScreen=True
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"michaelberrier" wrote in message
ups.com...
I manage a workbook that is used by lots of people, and therefore the
nuts and bolts are protected and locked. I would also like to hide all
the toolbars when this book opens. I understand that the user can
simply go to View--Toolbars to see them, but most of these people
won't have any reason to do that and I'd like to clean this interface
up as much as possible.

I've seen this done before, but I can't find the option(s) to make it
happen.

Thanks.

That works great! One thing, though. The Full Screen option moves my
sheet tabs down below the task bar if it doesn't autohide, and I can
just hear the users screaming already if they can't see the whole tab.

That said, I guess it would be better to write the code just to hide
and/or disable the other toolbars in just this book. Some generic code
in that direction would be appreciated.

Thanks again.

Nick Hodge wrote:
Michael

Try this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayFullScreen = False
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"michaelberrier" wrote in message
oups.com...
The full screen looks like a good idea, but it applies that attribute
to any workbook I open after that. Is there a way to turn that on for
only that workbook? I tried a Workbook_Close sub but that did nothing.

thanks
Nick Hodge wrote:
Michael

As you are saying "I understand that the user can simply go to
View--Toolbars", I am presuming you want 'Full screen view' If not you
will
need to explicitly hide all the commandbars in the window. The former
could
be done in the Workbook_Open() event. (To implement this right click on
the
Excel Icon at the top left of the window and select view code... and
paste
in here.

Private Sub Workbook_Open()
Application.DisplayFullScreen=True
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"michaelberrier" wrote in message
ups.com...
I manage a workbook that is used by lots of people, and therefore the
nuts and bolts are protected and locked. I would also like to hide all
the toolbars when this book opens. I understand that the user can
simply go to View--Toolbars to see them, but most of these people
won't have any reason to do that and I'd like to clean this interface
up as much as possible.

I've seen this done before, but I can't find the option(s) to make it
happen.

Thanks.

Michael

If you can get by with full screen it would be much better as you can be
sure of restoring the users default settings.If you iterate the command bar
collection and hide each one it finds visible, you are going to need to
store this somewhere to ensure you restore the correct ones otherwise you
will annoy more users who have specific toolbars showing. Most use a hidden
sheet to store this data

If you can be sure it is only standard, formatting, etc then that would be
ok but.....

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"michaelberrier" wrote in message
ups.com...
That works great! One thing, though. The Full Screen option moves my
sheet tabs down below the task bar if it doesn't autohide, and I can
just hear the users screaming already if they can't see the whole tab.

That said, I guess it would be better to write the code just to hide
and/or disable the other toolbars in just this book. Some generic code
in that direction would be appreciated.

Thanks again.

Nick Hodge wrote:
Michael

Try this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayFullScreen = False
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"michaelberrier" wrote in message
oups.com...
The full screen looks like a good idea, but it applies that attribute
to any workbook I open after that. Is there a way to turn that on for
only that workbook? I tried a Workbook_Close sub but that did nothing.

thanks
Nick Hodge wrote:
Michael

As you are saying "I understand that the user can simply go to
View--Toolbars", I am presuming you want 'Full screen view' If not
you
will
need to explicitly hide all the commandbars in the window. The former
could
be done in the Workbook_Open() event. (To implement this right click
on
the
Excel Icon at the top left of the window and select view code... and
paste
in here.

Private Sub Workbook_Open()
Application.DisplayFullScreen=True
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"michaelberrier" wrote in message
ups.com...
I manage a workbook that is used by lots of people, and therefore the
nuts and bolts are protected and locked. I would also like to hide
all
the toolbars when this book opens. I understand that the user can
simply go to View--Toolbars to see them, but most of these people
won't have any reason to do that and I'd like to clean this
interface
up as much as possible.

I've seen this done before, but I can't find the option(s) to make
it
happen.

Thanks.

With this group of users, I think to assume they are only using the
standard stuff is safe.

I guess what I need is the syntax to identify the individual toolbars
and the command to disable them. I think I understand the part about a
hidden sheet to store those settings.

Sounds like a WorkBook_Open & WorkBook_Close operation, right?
Nick Hodge wrote:
Michael

If you can get by with full screen it would be much better as you can be
sure of restoring the users default settings.If you iterate the command bar
collection and hide each one it finds visible, you are going to need to
store this somewhere to ensure you restore the correct ones otherwise you
will annoy more users who have specific toolbars showing. Most use a hidden
sheet to store this data

If you can be sure it is only standard, formatting, etc then that would be
ok but.....

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"michaelberrier" wrote in message
ups.com...
That works great! One thing, though. The Full Screen option moves my
sheet tabs down below the task bar if it doesn't autohide, and I can
just hear the users screaming already if they can't see the whole tab.

That said, I guess it would be better to write the code just to hide
and/or disable the other toolbars in just this book. Some generic code
in that direction would be appreciated.

Thanks again.

Nick Hodge wrote:
Michael

Try this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayFullScreen = False
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"michaelberrier" wrote in message
oups.com...
The full screen looks like a good idea, but it applies that attribute
to any workbook I open after that. Is there a way to turn that on for
only that workbook? I tried a Workbook_Close sub but that did nothing.

thanks
Nick Hodge wrote:
Michael

As you are saying "I understand that the user can simply go to
View--Toolbars", I am presuming you want 'Full screen view' If not
you
will
need to explicitly hide all the commandbars in the window. The former
could
be done in the Workbook_Open() event. (To implement this right click
on
the
Excel Icon at the top left of the window and select view code... and
paste
in here.

Private Sub Workbook_Open()
Application.DisplayFullScreen=True
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"michaelberrier" wrote in message
ups.com...
I manage a workbook that is used by lots of people, and therefore the
nuts and bolts are protected and locked. I would also like to hide
all
the toolbars when this book opens. I understand that the user can
simply go to View--Toolbars to see them, but most of these people
won't have any reason to do that and I'd like to clean this
interface
up as much as possible.

I've seen this done before, but I can't find the option(s) to make
it
happen.

Thanks.

Nick,
Thanks for the help. I found some code that does exactly what I want.
It's here if you want to have a look:
http://www.rondebruin.nl/menuid.htm#bar. I was able to make a
Worksheet_Open and BeforeClose set using this code that does exactly
what I want.

michaelberrier wrote:
With this group of users, I think to assume they are only using the
standard stuff is safe.

I guess what I need is the syntax to identify the individual toolbars
and the command to disable them. I think I understand the part about a
hidden sheet to store those settings.

Sounds like a WorkBook_Open & WorkBook_Close operation, right?
Nick Hodge wrote:
Michael

If you can get by with full screen it would be much better as you can be
sure of restoring the users default settings.If you iterate the command bar
collection and hide each one it finds visible, you are going to need to
store this somewhere to ensure you restore the correct ones otherwise you
will annoy more users who have specific toolbars showing. Most use a hidden
sheet to store this data

If you can be sure it is only standard, formatting, etc then that would be
ok but.....

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"michaelberrier" wrote in message
ups.com...
That works great! One thing, though. The Full Screen option moves my
sheet tabs down below the task bar if it doesn't autohide, and I can
just hear the users screaming already if they can't see the whole tab.

That said, I guess it would be better to write the code just to hide
and/or disable the other toolbars in just this book. Some generic code
in that direction would be appreciated.

Thanks again.

Nick Hodge wrote:
Michael

Try this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayFullScreen = False
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"michaelberrier" wrote in message
oups.com...
The full screen looks like a good idea, but it applies that attribute
to any workbook I open after that. Is there a way to turn that on for
only that workbook? I tried a Workbook_Close sub but that did nothing.

thanks
Nick Hodge wrote:
Michael

As you are saying "I understand that the user can simply go to
View--Toolbars", I am presuming you want 'Full screen view' If not
you
will
need to explicitly hide all the commandbars in the window. The former
could
be done in the Workbook_Open() event. (To implement this right click
on
the
Excel Icon at the top left of the window and select view code... and
paste
in here.

Private Sub Workbook_Open()
Application.DisplayFullScreen=True
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"michaelberrier" wrote in message
ups.com...
I manage a workbook that is used by lots of people, and therefore the
nuts and bolts are protected and locked. I would also like to hide
all
the toolbars when this book opens. I understand that the user can
simply go to View--Toolbars to see them, but most of these people
won't have any reason to do that and I'd like to clean this
interface
up as much as possible.

I've seen this done before, but I can't find the option(s) to make
it
happen.

Thanks.

Michael

Lightly tested

Sub RecordAndHideCommandBars()
Dim wks As Worksheet
Dim cmdBar As CommandBar
Dim x As Integer
On Error Resume Next
Set wks = Worksheets("HiddenData")
x = 1
For Each cmdBar In Application.CommandBars
If cmdBar.Visible = True Then
cmdBar.Enabled = False
wks.Cells(x, 1).Value = cmdBar.Name
x = x + 1
End If
Next cmdBar
ThisWorkbook.Save
End Sub

Sub RestoreCommandBars()
Dim lLastRow As Long
Dim myCell As Range
Dim wks As Worksheet
On Error Resume Next
Set wks = Worksheets("HiddenData")
lLastRow = wks.Range("A65536").End(xlUp).Row
For Each myCell In wks.Range("A1:A" & lLastRow)
Application.CommandBars(myCell.Value).Enabled = True
Next myCell
wks.Columns(1).Clear
ThisWorkbook.Save
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"michaelberrier" wrote in message
ups.com...
With this group of users, I think to assume they are only using the
standard stuff is safe.

I guess what I need is the syntax to identify the individual toolbars
and the command to disable them. I think I understand the part about a
hidden sheet to store those settings.

Sounds like a WorkBook_Open & WorkBook_Close operation, right?
Nick Hodge wrote:
Michael

If you can get by with full screen it would be much better as you can be
sure of restoring the users default settings.If you iterate the command
bar
collection and hide each one it finds visible, you are going to need to
store this somewhere to ensure you restore the correct ones otherwise you
will annoy more users who have specific toolbars showing. Most use a
hidden
sheet to store this data

If you can be sure it is only standard, formatting, etc then that would
be
ok but.....

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"michaelberrier" wrote in message
ups.com...
That works great! One thing, though. The Full Screen option moves my
sheet tabs down below the task bar if it doesn't autohide, and I can
just hear the users screaming already if they can't see the whole tab.

That said, I guess it would be better to write the code just to hide
and/or disable the other toolbars in just this book. Some generic code
in that direction would be appreciated.

Thanks again.

Nick Hodge wrote:
Michael

Try this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayFullScreen = False
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"michaelberrier" wrote in message
oups.com...
The full screen looks like a good idea, but it applies that
attribute
to any workbook I open after that. Is there a way to turn that on
for
only that workbook? I tried a Workbook_Close sub but that did
nothing.

thanks
Nick Hodge wrote:
Michael

As you are saying "I understand that the user can simply go to
View--Toolbars", I am presuming you want 'Full screen view' If not
you
will
need to explicitly hide all the commandbars in the window. The
former
could
be done in the Workbook_Open() event. (To implement this right
click
on
the
Excel Icon at the top left of the window and select view code...
and
paste
in here.

Private Sub Workbook_Open()
Application.DisplayFullScreen=True
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"michaelberrier" wrote in message
ups.com...
I manage a workbook that is used by lots of people, and therefore
the
nuts and bolts are protected and locked. I would also like to
hide
all
the toolbars when this book opens. I understand that the user
can
simply go to View--Toolbars to see them, but most of these
people
won't have any reason to do that and I'd like to clean this
interface
up as much as possible.

I've seen this done before, but I can't find the option(s) to
make
it
happen.

Thanks.