Customize CommandBars (disappear and reappear)

  • Thread starter Thread starter Geo Siggy
  • Start date Start date
G

Geo Siggy

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
 
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)
 
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
 
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)
 
Sounds like he doesn't have the array declared as a global/public variable.

--
Regards,
Tom Ogilvy

Bob Phillips said:
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)
Click to expand...
 
Bob:

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

Sigg
 
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,

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)
 
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
 
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
 
Back
Top