Creating Custom Menu in Personal.xls Workbook_Open

[Orion Cochrane]

I have a custom menu I use with personal macros used for different purposes.
Sometimes my settings get wiped out in Excel and I need to re-create the menu
from scratch. Can anyone tell me how to create the menu through VBA so that I
can trigger it in my PERSONAL.xls Workbook_Open routine?

The main menu control is called Reports, positioned at the end of the
Worksheet Menu Bar (CommandBars(1)). Reports has 2 sub-menus: Credit Cards,
and Petty Cash. When you click the Reports Menu, MenuEnable is triggered.

Credit Cards has 3 sub-menus: All, Current, and Journal Entry. All has 4
items (with macros): Run and/or Print Statements (CreditCardRunAll), Print
all Statements (CreditCardPrintAll), Clear Subtotals Only
(CreditCardClearSubtotalsAll), and Clear all Statements
(CreditCardClearReportAll). Current has 2 items (with macros): Run Statement
(CreditCardRunCurrent), and Clear Subtotals
(CreditCardClearSubtotalsCurrent). Journal Entry has 2 items (with macros):
Generate Journal Entry (CreditCardJE), and Clear Journal Entry
(CreditCardJEClear).

Petty Cash has 2 items (with macros): Run Report (PettyCashRun), and Run
Journal Entry (PettyCashJE).

The table below summarizes this post (hopefully it shows up nice).

All I need is code to create this menu and map the right macros to each
item. This will save me a lot of time if it can be done. Big thanks in
advance.

Name Type Macro
Reports Control MenuEnable
Credit Cards Control (None)
All Control (None)
Run and/or Print Statements Item CreditCardRunAll
Print all Statements Item CreditCardPrintAll
Clear Subtotals Only Item CreditCardClearSubtotalsAll
Clear all Statements Item CreditCardClearReportAll
Current Control (None)
Run Statement Item CreditCardRunCurrent
Clear Subtotals Item CreditCardClearSubtotalsCurrent
Journal Entry Control (None)
Generate Journal Entry Item CreditCardJE
Clear Journal Entry Item CreditCardJEClear
Petty Cash Control (None)
Run Report Item PettyCashRun
Run Journal Entry Item PettyCashJE

 

[Jim Rech]

You might want to try John Walkenbach's table driven approach to customized
commandbars:

http://spreadsheetpage.com/index.php/file/menu_maker/

--
Jim
|I have a custom menu I use with personal macros used for different
purposes.
| Sometimes my settings get wiped out in Excel and I need to re-create the
menu
| from scratch. Can anyone tell me how to create the menu through VBA so
that I
| can trigger it in my PERSONAL.xls Workbook_Open routine?
|
| The main menu control is called Reports, positioned at the end of the
| Worksheet Menu Bar (CommandBars(1)). Reports has 2 sub-menus: Credit
Cards,
| and Petty Cash. When you click the Reports Menu, MenuEnable is triggered.
|
| Credit Cards has 3 sub-menus: All, Current, and Journal Entry. All has 4
| items (with macros): Run and/or Print Statements (CreditCardRunAll), Print
| all Statements (CreditCardPrintAll), Clear Subtotals Only
| (CreditCardClearSubtotalsAll), and Clear all Statements
| (CreditCardClearReportAll). Current has 2 items (with macros): Run
Statement
| (CreditCardRunCurrent), and Clear Subtotals
| (CreditCardClearSubtotalsCurrent). Journal Entry has 2 items (with
macros):
| Generate Journal Entry (CreditCardJE), and Clear Journal Entry
| (CreditCardJEClear).
|
| Petty Cash has 2 items (with macros): Run Report (PettyCashRun), and Run
| Journal Entry (PettyCashJE).
|
| The table below summarizes this post (hopefully it shows up nice).
|
| All I need is code to create this menu and map the right macros to each
| item. This will save me a lot of time if it can be done. Big thanks in
| advance.
|
| Name Type Macro
| Reports Control MenuEnable
| Credit Cards Control (None)
| All Control (None)
| Run and/or Print Statements Item CreditCardRunAll
| Print all Statements Item CreditCardPrintAll
| Clear Subtotals Only Item CreditCardClearSubtotalsAll
| Clear all Statements Item CreditCardClearReportAll
| Current Control (None)
| Run Statement Item CreditCardRunCurrent
| Clear Subtotals Item CreditCardClearSubtotalsCurrent
| Journal Entry Control (None)
| Generate Journal Entry Item CreditCardJE
| Clear Journal Entry Item CreditCardJEClear
| Petty Cash Control (None)
| Run Report Item PettyCashRun
| Run Journal Entry Item PettyCashJE
|
| --
| I am running on Excel 2003, unless otherwise stated.

 

[Orion Cochrane]

This is great, and I have seen his approach before. Do you think that I could
tailor this file to my menu, bring over my other PERSONAL modules, and make
the menu file my new PERSONAL.xls? That would be so much easier than running
2 files, since my PERSONAL.xls is already created.

 

[Orion Cochrane]

I can't see the file. I tried unhiding the file thru Explorer, and I can't
see the file still.

 

[Orion Cochrane]

The table approach didn't give me the desired results, but the code helped me
learn some principles behind menu creation. I have a menu with submenus in
them. Declaring various menu objects is helping me a lot for the placement of
the items.

Thanks, all!