Customize CommandBars (disappear and reappear)
 

In my Excel application programmed with VBA I want that only one specia
commandbar appears with a few selected buttons. This maybe easy t
program in VBA. But the problem are the existing commandbars. I wan
that all existing commandbars disappear when the application is opened
but re-appear in the exact structure (bars and buttons) as they wer
before using my application, independently of the Excel someone use
and the commandbars somebody uses.

Thanx Sigg

--
Message posted from http://www.ExcelForum.com

Customize CommandBars (disappear and reappear)
 

Siggy,

Here is some code to hide them and restore them. This code would go in the
ThisWorkbook code module. Your code to create the special CBs should be run
before you hide the others.

Dim aryCBs

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim i As Long

For i = LBound(aryCBs) To UBound(aryCBs)
Application.CommandBars(aryCBs(i)).Visible = True
Next i

End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim i As Long

ReDim aryCBs(0)
For Each oCB In Application.CommandBars
If oCB.Visible Then
If oCB.Name < "Worksheet Menu Bar" Then
ReDim Preserve aryCBs(i)
aryCBs(i) = oCB.Name
oCB.Visible = False
i = i + 1
End If
End If
Next oCB

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Geo Siggy " wrote in message
...
In my Excel application programmed with VBA I want that only one special
commandbar appears with a few selected buttons. This maybe easy to
program in VBA. But the problem are the existing commandbars. I want
that all existing commandbars disappear when the application is opened,
but re-appear in the exact structure (bars and buttons) as they were
before using my application, independently of the Excel someone uses
and the commandbars somebody uses.

Thanx Siggy


---
Message posted from http://www.ExcelForum.com/

Customize CommandBars (disappear and reappear)
 

Thats it
Thanx to Bob
Siggy


---
Message posted from http://www.ExcelForum.com/

Customize CommandBars (disappear and reappear)
 

Hi Bob or anybody

I tested the code in my application, and there is still a run tim
error No. 13 ('types incompatible') when reaching the line
For i = LBound(aryCBs) To UBound(aryCBs)
when closing the application.
The disappearing of the commandbars works the reappearance dont.
I tried some changes but I dont find the clue.

Sigg

--
Message posted from http://www.ExcelForum.com

Customize CommandBars (disappear and reappear)
 

Hi Geo,

It works for me so I am at a loss. Why don't you mail me the workbook (bob
.. phillips @ tiscali . co . uk - remove the spaces), and I will see what I
can do.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Geo Siggy " wrote in message
...
Hi Bob or anybody

I tested the code in my application, and there is still a run time
error No. 13 ('types incompatible') when reaching the line
For i = LBound(aryCBs) To UBound(aryCBs)
when closing the application.
The disappearing of the commandbars works the reappearance dont.
I tried some changes but I dont find the clue.

Siggy


---
Message posted from http://www.ExcelForum.com/

Customize CommandBars (disappear and reappear)
 

Sounds like he doesn't have the array declared as a global/public variable.

--
Regards,
Tom Ogilvy

Bob Phillips wrote in message
...
Hi Geo,

It works for me so I am at a loss. Why don't you mail me the workbook
(bob
. phillips @ tiscali . co . uk - remove the spaces), and I will see what I
can do.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Geo Siggy " wrote in message
...
Hi Bob or anybody

I tested the code in my application, and there is still a run time
error No. 13 ('types incompatible') when reaching the line
For i = LBound(aryCBs) To UBound(aryCBs)
when closing the application.
The disappearing of the commandbars works the reappearance dont.
I tried some changes but I dont find the clue.

Siggy


---
Message posted from http://www.ExcelForum.com/

Customize CommandBars (disappear and reappear)
 

Bob:

many thanks for your help
but I'm not allowed to give away that application ...

Sigg

--
Message posted from http://www.ExcelForum.com

Customize CommandBars (disappear and reappear)
 

Can you not strip it down to the fundamentals, test that it still doesn't
work, and mail that to me?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Geo Siggy " wrote in message
...
Bob:

many thanks for your help
but I'm not allowed to give away that application ...

Siggy


---
Message posted from http://www.ExcelForum.com/

Customize CommandBars (disappear and reappear)
 

Geo,

Did you also see Tom's suggestion. The aryCBs needs to be declared outside
of the modules, so that it is available to both. Did you do that?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Geo Siggy " wrote in message
...
Bob:

many thanks for your help
but I'm not allowed to give away that application ...

Siggy


---
Message posted from http://www.ExcelForum.com/

Customize CommandBars (disappear and reappear)
 

Hi Bob, hi Tom ...

maybe thats the clue to declare the array as public, but as what I hav
to declare it ...
public aryCBs as ...?

PS: If you like to check iit out I append the file called test2.xl
(reduced only to that code).

Thanks again.

Sigg

--
Message posted from http://www.ExcelForum.com

Customize CommandBars (disappear and reappear)
 

I think I got it to work
the easiest way with

Public aryCBs As Variant

(No more run time error.)

But there's still one problem: When I remove the application workboo
without closing Excel the original commandbars don't come back. So
have to close Excel to make the commandbars reappear.
How to make it possible to make the original commandbars reappear whe
I only close the workbook or worksheets not the Excel program.


Thanks again Sigg

--
Message posted from http://www.ExcelForum.com