Back in Excel 4.0 I used the following to take a snapshoot of what
toolbars where visible\active then remove them

SET.NAME("toolbarStatus",GET.TOOLBAR(9))
IF(NOT(ISERROR(toolbarStatus)))
FOR("barCount",1,COLUMNS(toolbarStatus))
SHOW.TOOLBAR(INDEX(toolbarStatus,1,barCount),FALSE )
NEXT()
END.IF()

Then to put them back I used:

IF(NOT(ISERROR(toolbarStatus)))
FOR("barCount",1,COLUMNS(toolbarStatus))
SHOW.TOOLBAR(INDEX(toolbarStatus,1,barCount),TRUE)
NEXT()
END.IF()

Looking for a VBA equivalent. -Randy-

Randy

Something like this

Sub StoreCB()

Dim cb As CommandBar
Dim cbVis() As Boolean
Dim cbPos() As MsoBarPosition
Dim cbRw() As Long
Dim i As Long

ReDim cbVis(1 To Application.CommandBars.Count)
ReDim cbPos(1 To Application.CommandBars.Count)
ReDim cbRw(1 To Application.CommandBars.Count)

i = 1
For Each cb In Application.CommandBars
cbVis(i) = cb.Visible
cbPos(i) = cb.Position
cbRw(i) = cb.RowIndex
i = i + 1
Next cb

End Sub

Make those arrays module-level and use them to reset everything in another
sub.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Randal W. Hozeski" wrote in message
news:w5BIb.15195$I07.38816@attbi_s53...
Back in Excel 4.0 I used the following to take a snapshoot of what
toolbars where visible\active then remove them

SET.NAME("toolbarStatus",GET.TOOLBAR(9))
IF(NOT(ISERROR(toolbarStatus)))
FOR("barCount",1,COLUMNS(toolbarStatus))
SHOW.TOOLBAR(INDEX(toolbarStatus,1,barCount),FALSE )
NEXT()
END.IF()

Then to put them back I used:

IF(NOT(ISERROR(toolbarStatus)))
FOR("barCount",1,COLUMNS(toolbarStatus))
SHOW.TOOLBAR(INDEX(toolbarStatus,1,barCount),TRUE)
NEXT()
END.IF()

Looking for a VBA equivalent. -Randy-

Dick:
Thanks for the information. I copied the code
into a mod sheet and ran it. It does not look
as if anything happened. (at least no errors)
Was this suppose to remember them and then remove
them?

You mentioned "Make those arrays module-level"
to add them back. Not to good w/ module-level
or arrays for that matter. I was just going to
add a Sub to a Auto_Close that put them back.

Please advise. -Randy-

..





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Randy

All that sub does is demonstrate the objects and properties that you'll need
to hide and show commandbars. Here's a better way from John Green. He runs
this from a commandbutton, but you could put the top half in an open event
and the bottom half in a beforeclose event.

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Set x = Application.CommandBars("Worksheet Menu Bar")
If x.Enabled Then
x.Enabled = False
With ActiveWindow
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
End With
Application.DisplayFullScreen = True
Else
x.Enabled = True
With ActiveWindow
.DisplayGridlines = True
.DisplayHeadings = True
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
.DisplayWorkbookTabs = True
End With
Application.DisplayFullScreen = False
End If
Application.ScreenUpdating = True
End Sub

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Randal W. Hozeski" wrote in message
...
Dick:
Thanks for the information. I copied the code
into a mod sheet and ran it. It does not look
as if anything happened. (at least no errors)
Was this suppose to remember them and then remove
them?

You mentioned "Make those arrays module-level"
to add them back. Not to good w/ module-level
or arrays for that matter. I was just going to
add a Sub to a Auto_Close that put them back.

Please advise. -Randy-

.





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks again!! That does work for on screen elements and
I am using that on screen objects from the Tools/Options Menu.

Let me explain the issue a bit more....
The application written is used by a number of users.
Each user has certain (even custom) toolbars visible, so
what works for one user will not work for another, unless
you modify each persons, then after they add/delete them
........

I believe that the first example given as a solution is
a bit more on track. I need to search what toolbars are
visible, remember them, delete with. (using it in an
Auto_Open) Then on closing the workbook (Auto_Close) take
what it remembered and put them back.

This one proved to be a bit tougher then I expected.
-Randy-




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

DISREGARD MY LAST NOTE!!! IT WORKED FINE!!
-Randy-

I am interested in more of Mr. Green's work. Is
there any type of referrence URL's or books that
I could get?


..


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!