Trouble hiding commandbars
 

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

Trouble hiding commandbars
 

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


"?B?QW5keQ==?=" wrote:

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

Trouble hiding commandbars
 

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

--
dehinso
-----------------------------------------------------------------------
dehinson's Profile: http://www.excelforum.com/member.php...fo&userid=1546
View this thread: http://www.excelforum.com/showthread.php?threadid=27044