toolbars

[Hein]

Hi,
I'm new at programming in Excel and have the following question:

is it possible that, if I open an Excel-file, all visible toolbars are
put to invisible.

If I close the file, does this mean that if I open another file the
toolbars are still invisible?
If I have to make them visible again, how can I do this?

Hope somebody can help me?

Thanks
Hein

 

[Roman]

Hi Hein,
please before you ask something look for an answer in this discussion.
The point is that your question was answered t o d a y.

 

[Hein]

Roman
You're right and I'm sorry for the inconvenience

However:
1.
the answer is indeed given today, but AFTER I've posted my thread
2.
I'm in kind of a hurry to get this sorted, I'm new to prgogramming i
Excel, I'm new to this site (new member today) and didn't kno
how/where to look.

I have a question about the other answer:
I've carefully typed in the code but it doesn't work.
How can I get it to work if I open the file?
Hope you can help me?

Thanks
Hei

 

[Roman]

Hi Hein,
you can call the DM Unseen's code after a Workbook_Open event and
Workbook_close event passing required bHide variable. You can use
Workbook_WindowActivate and Workbook_WindowDeactivate events as well.

 

[Roman]

This could be a good solution too:

http://www.ozgrid.com/VBA/toolbar-remove-restore.htm

 

[DM Unseen]

Hein,

no matter, you're right it was simple coding to help someone, I've did
some more testing and made it more robust, here you go:


Sub showhide(Optional bHide As Boolean = False)
Dim cmb As CommandBar
Static col As New Collection


If bHide Then
For Each cmb In Application.CommandBars
If cmb.Type = msoBarTypeMenuBar Or cmb.Type = msoBarTypeNormal Then
If cmb.Visible Then
cmb.Enabled = False
If cmb.Visible Then cmb.Visible = False
col.Add cmb, cmb.name
End If
End If
Next cmb
Else
If col Is Nothing Or col.Count = 0 Then
For Each cmb In Application.CommandBars
If cmb.Type = msoBarTypeMenuBar Or cmb.Type = msoBarTypeNormal
Then
If Not cmb.Visible Then
cmb.Enabled = True
If (Not cmb.Visible) And cmb.Enabled Then cmb.Visible =
True
End If
End If
Next cmb
Else
For Each cmb In col
If cmb.ID <> 265 Then cmb.Visible = True
cmb.Enabled = True
Next cmb
End If
Set col = Nothing
End If


End Sub

DM Unseen AKA Martijn Evers

 

[DM Unseen]

Oops, small logic typo (grr)

PS Roman, my current proc will actually try to remember toolbar
settings, the ozgrid one does not do this.

Sub showhide(Optional bHide As Boolean = False)
Dim cmb As CommandBar
Static col As New Collection


If bHide Then
For Each cmb In Application.CommandBars
If cmb.Type = msoBarTypeMenuBar Or cmb.Type = msoBarTypeNormal Then
If cmb.Visible Then
cmb.Enabled = False
If cmb.Visible Then cmb.Visible = False
col.Add cmb, cmb.name
End If
End If
Next cmb
Else
If col Is Nothing Or col.Count = 0 Then
For Each cmb In Application.CommandBars
If cmb.Type = msoBarTypeMenuBar Or cmb.Type = msoBarTypeNormal
Then
If Not cmb.Visible Or Not cmb.Enabled Then
cmb.Enabled = True
If (Not cmb.Visible) And cmb.Enabled Then cmb.Visible =
True
End If
End If
Next cmb
Else
For Each cmb In col
If Not cmb.Visible Or Not cmb.Enabled Then
cmb.Enabled = True
If (Not cmb.Visible) And cmb.Enabled Then cmb.Visible =
True
End If
Next cmb
End If
Set col = Nothing
End If


End Sub


Dm Unseen