How to identify active Menu Bars

[Matz]

Hi,

I need a sub procedure that identifies all active Menu Bars when opening a
workbook.

- Then store the ID
- disable all found Menus
- active a customized Menu (That works already)

before closing Workbook:
- disable customized Menu (That works already)
- enable alle menus that was active in the beginning

I tried something with .findcontrols but I coudn't get it running.

Thanks!!

[DCSwearingen]

This isn't an exact answer to you question, but maybe it can be adapted for your purpose?

I have since adapted to store in a worksheet array rather tha in a global array, but that code is at work and I will be away from work for several weeks.

See the following:
http://www.excelforum.com/showthread.php?t=497189

[Bob Phillips]

Option Explicit

Private mFormulaBar

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB

Application.DisplayFormulaBar = mFormulaBar
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = False
Next oCB

mFormulaBar = Application.DisplayFormulaBar
Application.DisplayFormulaBar = False
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Matz" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I need a sub procedure that identifies all active Menu Bars when opening a
> workbook.
>
> - Then store the ID
> - disable all found Menus
> - active a customized Menu (That works already)
>
> before closing Workbook:
> - disable customized Menu (That works already)
> - enable alle menus that was active in the beginning
>
> I tried something with .findcontrols but I coudn't get it running.
>
> Thanks!!

[Matz]

Hey Bob

you are great! Perfect - just copy paste....
Thanks for the sunday highlight in VBA :-)
Matz

"Bob Phillips" wrote:

> Option Explicit
>
> Private mFormulaBar
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Dim oCB As CommandBar
> For Each oCB In Application.CommandBars
> oCB.Enabled = True
> Next oCB
>
> Application.DisplayFormulaBar = mFormulaBar
> End Sub
>
> Private Sub Workbook_Open()
> Dim oCB As CommandBar
> For Each oCB In Application.CommandBars
> oCB.Enabled = False
> Next oCB
>
> mFormulaBar = Application.DisplayFormulaBar
> Application.DisplayFormulaBar = False
> End Sub
>
> 'This is workbook event code.
> 'To input this code, right click on the Excel icon on the worksheet
> '(or next to the File menu if you maximise your workbooks),
> 'select View Code from the menu, and paste the code
>
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Matz" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I need a sub procedure that identifies all active Menu Bars when opening a
> > workbook.
> >
> > - Then store the ID
> > - disable all found Menus
> > - active a customized Menu (That works already)
> >
> > before closing Workbook:
> > - disable customized Menu (That works already)
> > - enable alle menus that was active in the beginning
> >
> > I tried something with .findcontrols but I coudn't get it running.
> >
> > Thanks!!
>
>
>