Startup

T

Tanya

Hi
Is it possible to have only the menubar visible on startup with an Excel
workbook? if so what would the script look like?
cheers
Tanya
 
R

Rick Rothstein \(MVP - VB\)

You can hide the toolbars using the code below. Insert a Module into your
project (Insert/Module from the VB editor's menu bar) and copy/paste the
code below into its code window....

'******* Start of Code *******
Public C As New Collection

Public Sub HideToolBars()
Dim T As Object
Set C = Nothing
For Each T In Toolbars
If T.Visible Then C.Add T
T.Visible = False
Next
End Sub

Public Sub ShowToolBars()
Dim T As Object
For Each T In C
T.Visible = True
Next
End Sub
'******* End of Code *******

You can call the HideToolBars subroutine in the Workbook Open event to hide
the toolbars. When you want to display them again, run the ShowToolBars
subroutine. While I haven't tested it, I would expect that if you hide the
toolbars, save the Excel workbook and then CLOSE it, that the ShowToolBars
subroutine would not restore them again (without doing so through Excel's
View/Toolbars menu item).

Rick
 
R

Rick Rothstein \(MVP - VB\)

You can call the HideToolBars subroutine in the Workbook Open
event to hide the toolbars.

Of course, you can the HideToolBars at any time in order to make them
invisible; I just mentioned the Workbook Open event because your initial
posting suggested you didn't want them to be seen when the workbook was
first opened.

Rick
 
J

john

Tanya,
this should do what you want:
When workbook opens and assuming user enables macros toolbars except menubar
will not be visible.
When you close workbook, all should be restored.

Hope useful

'place code in Thisworkbook
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Restore Bars
HideBars (xlOff)
End Sub

'place code in Thisworkbook
Private Sub Workbook_Open()
'Hide Bars
HideBars (xlOn)
End Sub

'place this code in normal module
Sub HideBars(state)
Static myoldbars As New Collection
Dim mybar

If state = xlOn Then
For Each mybar In Application.CommandBars
If mybar.Type <> 1 And mybar.Visible Then
myoldbars.Add mybar
mybar.Visible = False
End If
Next mybar

Else
'restore bars
For Each mybar In myoldbars
mybar.Visible = True
Next

End If
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top