ExcelBanter - Show no toolbars in specific book

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.