toolbars

[POPPY]

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

 

[Tom Ogilvy]

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.

 

[POPPY]

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

 

[Bob Phillips]

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]

Great!
Thanks Bob )

-----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)




.