I want to hide all menus and status bar from Excel 2003

[Michael]

Hi there,


I'm working on an Excel 2003 application and I would like to completely have
a way to hide all menus and status bar from Excel 2003. Essentially I want
it to appear as if this is a Win32 application. Any ideas? Also, how can I
turn them back on when I want to work back with Excel?

 

[Nick Hodge]

Michael

Here is a starter for 10... you could of course put the code in the first
sub in the Workbook_Open() event and the second code into the
Workbook_BeforeClose() event, but I list it below in separate subs. (It
places the names of the CommandBars on a veryhidden sheet)

Sub Dictator()
Dim stateStore As Worksheet
Dim cmBar As CommandBar
Dim x As Integer
Set stateStore = Worksheets.Add
stateStore.Name = "StateStore"
stateStore.Visible = xlSheetVeryHidden
With Application
.Caption = "My Application"
.DisplayFormulaBar = False
.DisplayScrollBars = False
.DisplayStatusBar = False
.WindowState = xlMaximized
End With
With ActiveWindow
.DisplayHeadings = False
.DisplayWorkbookTabs = False
End With
For Each cmBar In Application.CommandBars
If cmBar.Visible Then
stateStore.Range("A" & x + 1).Value = cmBar.Name
cmBar.Enabled = False
x = x + 1
End If
Next cmBar
End Sub

Sub UndoDictator()
Dim stateStore As Worksheet
Dim myCell As Range
Set stateStore = Worksheets("StateStore")
With Application
.Caption = ""
.DisplayFormulaBar = True
.DisplayScrollBars = True
.DisplayStatusBar = True
End With
With ActiveWindow
.DisplayHeadings = True
.DisplayWorkbookTabs = True
End With
For Each myCell In Worksheets("StateStore").UsedRange
Application.CommandBars(myCell.Text).Enabled = True
Next myCell
With Application
.DisplayAlerts = False
With stateStore
.Visible = xlSheetVisible
.Delete
End With
.DisplayAlerts = True
End With
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog: www.nickhodge.co.uk/blog/

FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007
www.officeusergroup.co.uk

 

[Harlan Grove]

Sub Dictator() ....
Set stateStore = Worksheets.Add

....

Either a nasty possible bug or a huge inefficiency. If the workbook
would ever be saved, it'd either be saved with a StateStore worksheet
in it, in which case there'd already be a StateStore worksheet the
next time the user opens the workbook, or you'd need to bracket save
operations between UndoDictator and Dictator calls. Better to create
it once, and just clear it in UndoDictator. Best to build it into the
original workbook so it doesn't need to be created by macro.

With Application ....
.DisplayScrollBars = False

....

Unclear OP wanted to eliminate scroll bars.

.WindowState = xlMaximized

....

Unclear OP wanted the application window maximized. Besides, better/
cleaner/more flexible to use a shortcut to launch the application in
the desired window state than hardcode the window state into the
application.

I'd guess you meant to put this into the With ActiveWindow block to
maximize the document window within the application window.

For Each cmBar In Application.CommandBars
If cmBar.Visible Then
stateStore.Range("A" & x + 1).Value = cmBar.Name
cmBar.Enabled = False
x = x + 1
End If
Next cmBar

....

Quibble: maybe better to use a defined name to store a concatenated
list of visible command bars than use an entire new worksheet.

 

[Michael]

Thanks Nick! This works although I have to make some modiciations to make it
work with something else. Nevertheless, thanks!

-- Michael

 

[Nick Hodge]

Harlan

The maximize should really have been in both. It wasn't also supposed to be
a finished app. I suspect the OP would have done a lot of other things with
it, as confirmed with his later post, some of which could have been to stop
a user saving, etc, etc, etc.

I think a better route would be a defined name and I was going to use that
however if you add all the names together I think it may overflow the
maximum length. Although unlikely a user would have that many bars enabled
;-)

--
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog: www.nickhodge.co.uk/blog/

FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007
www.officeusergroup.co.uk