Trouble hiding commandbars

[Guest]

Hi,
I have a sheet and I want to hide any existing commandbars on openning the
sheet.
I'm using the code below, but it errors with -2147467259 (8004005) -
Automation Error Unspecified Error

Dim cmdbar As CommandBar
For Each cmdbar In Application.CommandBars
If cmdbar.Visible Then
cmdbar.Visible = False '<- errors on this line
End If
Next

Does anyone have any ideas?
NB We're in a mixed Excel'97 & Excel'02 environment

Also, presumably I should be saving the state/name of each visible
commandbar and then setting them back to visible on exiting
(workbook.deactivate?
Are there any neat ways of achieving this?

Many thanks for any help.
Andy

 

[keepITcool]

DONT remove the commandbars.
most users will hate you for it.

ELSE

store the protection property of the commandbar (it's a long)
set protection to 0
THEN set the ENABLED property to false
then set the protection property back to stored value.


BUT I REPEAT

(IF YOU DONT PUT IT BACK EXACTLY THE WAY THE WERE!)
and that means under ALL circumstances.. including
errors/stateloss etc..

and do it for all views

... some people will get in touch with you...
and they wont be happy



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[dehinson]

This is what I do to hide and un-hide command bars.....

' Emmulate Full Screen By Turning Off Active Toolbars
Call Hide_Toolbars(CurrentToolSet, True)

' Restore Toolbars And Return To Normal Screen
Call Hide_Toolbars(CurrentToolSet, False)

Static Function Hide_Toolbars(ToolSet() As Boolean, Mode As Boolean)

Dim i As Integer

If Mode = True Then
Application.WindowState = xlMaximized
Application.DisplayFormulaBar = False
For i = 1 To Application.Toolbars.Count
ToolSet(i) = Application.Toolbars(i).Visible
Application.Toolbars(i).Visible = False
Next i
Else
Application.DisplayFormulaBar = True
For i = 1 To Application.Toolbars.Count
Application.Toolbars(i).Visible = ToolSet(i)
Next i
End If

End Function

It works pretty well. Probably could tweak it some more but I have no
had any complaints it require me to do so.

I hope this helps