Hello

How can I make all the toolbars & menu bars disappear when
I open a particular workbook, but re-appear when the
workbook is closed?

I'm not sure if it's possible to do this for one workbook
or whether it has to apply to the whole application.
Applying to an individual workbook would be good.

thanks

There is no applying. You have to write the code in the appropriate events
to loop through all the toolbars and set the visible property to false
(except commandbars 1 and 2 which have to be disabled), and then restore
them when appropriate.

--
Regards,
Tom Ogilvy

"POPPY" wrote in message
...
Hello

How can I make all the toolbars & menu bars disappear when
I open a particular workbook, but re-appear when the
workbook is closed?

I'm not sure if it's possible to do this for one workbook
or whether it has to apply to the whole application.
Applying to an individual workbook would be good.

thanks

Thanks Tom
Do you know what the code would be to do that?
I'm not sure how to refer to the different toolbars in vba.

thanks
-----Original Message-----
There is no applying. You have to write the code in the
appropriate events
to loop through all the toolbars and set the visible
property to false
(except commandbars 1 and 2 which have to be disabled),
and then restore
them when appropriate.

--
Regards,
Tom Ogilvy

"POPPY" wrote in
message
...
Hello

How can I make all the toolbars & menu bars disappear
when
I open a particular workbook, but re-appear when the
workbook is closed?

I'm not sure if it's possible to do this for one
workbook
or whether it has to apply to the whole application.
Applying to an individual workbook would be good.

thanks


.

Here is an example

Dim aryCBs

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim i As Long
For i = LBound(aryCBs, 1) To UBound(aryCBs, 1)
Application.CommandBars(aryCBs(i)).visible = True
Next i
Application.CommandBars("Worksheet Menu Bar").Enabled = True
End Sub

Private Sub Workbook_Open()
Dim cb As CommandBar
Dim j As Long

Application.CommandBars("Worksheet Menu Bar").Enabled = False
For Each cb In Application.CommandBars
If cb.Name < "Worksheet Menu Bar" Then
If cb.visible = True Then
If j = 0 Then
ReDim aryCBs(0)
Else
ReDim Preserve aryCBs(j)
End If
aryCBs(j) = cb.Name
cb.visible = False
j = j + 1
End If
End If
Next cb

End Sub

Goes in the ThisWorkbook code module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"POPPY" wrote in message
...
Thanks Tom
Do you know what the code would be to do that?
I'm not sure how to refer to the different toolbars in vba.

thanks
-----Original Message-----
There is no applying. You have to write the code in the
appropriate events
to loop through all the toolbars and set the visible
property to false
(except commandbars 1 and 2 which have to be disabled),
and then restore
them when appropriate.

--
Regards,
Tom Ogilvy

"POPPY" wrote in
message
...
Hello

How can I make all the toolbars & menu bars disappear
when
I open a particular workbook, but re-appear when the
workbook is closed?

I'm not sure if it's possible to do this for one
workbook
or whether it has to apply to the whole application.
Applying to an individual workbook would be good.

thanks


.

Great!
Thanks Bob :o)
-----Original Message-----
Here is an example

Dim aryCBs

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim i As Long
For i = LBound(aryCBs, 1) To UBound(aryCBs, 1)
Application.CommandBars(aryCBs(i)).visible = True
Next i
Application.CommandBars("Worksheet Menu Bar").Enabled
= True
End Sub

Private Sub Workbook_Open()
Dim cb As CommandBar
Dim j As Long

Application.CommandBars("Worksheet Menu Bar").Enabled
= False
For Each cb In Application.CommandBars
If cb.Name < "Worksheet Menu Bar" Then
If cb.visible = True Then
If j = 0 Then
ReDim aryCBs(0)
Else
ReDim Preserve aryCBs(j)
End If
aryCBs(j) = cb.Name
cb.visible = False
j = j + 1
End If
End If
Next cb

End Sub

Goes in the ThisWorkbook code module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"POPPY" wrote in
message
...
Thanks Tom
Do you know what the code would be to do that?
I'm not sure how to refer to the different toolbars in
vba.

thanks
-----Original Message-----
There is no applying. You have to write the code in
the
appropriate events
to loop through all the toolbars and set the visible
property to false
(except commandbars 1 and 2 which have to be disabled),
and then restore
them when appropriate.

--
Regards,
Tom Ogilvy

"POPPY" wrote in
message
...
Hello

How can I make all the toolbars & menu bars disappear
when
I open a particular workbook, but re-appear when the
workbook is closed?


I'm not sure if it's possible to do this for one
workbook
or whether it has to apply to the whole application.
Applying to an individual workbook would be good.

thanks


.



.