Assigning a macro to a Menu Bar option

S

Stuart Grant

I have a workbook which starts by adding to new items to the standard menu
bar List and Transactions. (I realise List is perhaps not a good name since
it is used by Excel but it hasn't caused any trouble and I can change it if
it does).

The MakeSubMenus is as follows

Sub MakeSubMenus()
Dim Bar As Object

Set Bar = MenuBars(xlWorksheet)
Bar.Reset

'Create new Menus
Bar.Menus.Add Caption:="&List"
Bar.Menus.Add Caption:="&Transactions"
Set ListMenu = Bar.Menus("&List")
Set TransactionsMenu = Bar.Menus("&Transactions")

'Create menu items

With ListMenu.MenuItems
.AddMenu "&Account Details"
.AddMenu "&Income/Expenditure"
.AddMenu "&Balance Sheet"
End With

With TransactionsMenu.MenuItems
.AddMenu "&Record"
.AddMenu "&List by Account"
.AddMenu "List by &Month"
End With

End Sub

It works fine. I want however to add a macro to each of the menu items -
Account Details etc. I tried assigning the macros after the program starts
using Customize etc.. That works but when I end the program, next time I
start, the assignment has gone. I assume I have to use an OnAction
statement attached to each of the menu items to specify the macro(Sub) but I
can't figure out the syntax. I guess I'm not Object-Orientated enough !

Can anyone help.

Stuart
 
D

Dave Peterson

The menubar is still supported in excel, but was superseded by commandbars in
xl97 (IIRC).

Maybe something like this would help. It creates the items and assigns the
macros when the workbook opens (using auto_open). It cleans up after itself
when the workbook closes (using auto_close).

Option Explicit
Sub auto_open()

Dim cBar As CommandBar
Dim ListMenu As CommandBarControl
Dim SubMenu As CommandBarControl
Dim TransactionsMenu As CommandBarControl

Call RemoveMenus

Set cBar = Application.CommandBars("Worksheet Menu Bar")

With cBar
Set ListMenu = cBar.Controls.Add(Type:=msoControlPopup, _
before:=.Controls.Count, temporary:=True)
ListMenu.Caption = "&List"

With ListMenu.Controls.Add(Type:=msoControlButton, temporary:=True)
.Caption = "&Account Details"
.OnAction = "'" & ThisWorkbook.Name & "'!ListAcctDetails"
.FaceId = 103
End With

With ListMenu.Controls.Add(Type:=msoControlButton, temporary:=True)
.Caption = "&Income/Expenditure"
.OnAction = "'" & ThisWorkbook.Name & "'!ListIncExp"
.FaceId = 103
End With

With ListMenu.Controls.Add(Type:=msoControlButton, temporary:=True)
.Caption = "&Balance Sheet"
.OnAction = "'" & ThisWorkbook.Name & "'!ListBalSheet"
.FaceId = 103
End With

'===========

Set TransactionsMenu = cBar.Controls.Add(Type:=msoControlPopup, _
before:=.Controls.Count, temporary:=True)
TransactionsMenu.Caption = "&Transactions"

With TransactionsMenu.Controls.Add(Type:=msoControlButton, _
temporary:=True)
.Caption = "&Record"
.OnAction = "'" & ThisWorkbook.Name & "'!TranRecord"
.FaceId = 103
End With

With TransactionsMenu.Controls.Add(Type:=msoControlButton, _
temporary:=True)
.Caption = "&List by Account"
.OnAction = "'" & ThisWorkbook.Name & "'!TranListAcc"
.FaceId = 103
End With

With TransactionsMenu.Controls.Add(Type:=msoControlButton, _
temporary:=True)
.Caption = "List by &Month"
.OnAction = "'" & ThisWorkbook.Name & "'!TranListByMonth"
.FaceId = 103
End With

End With

End Sub
Sub auto_close()
Call RemoveMenus
End Sub
Sub RemoveMenus()

Dim cBar As CommandBar
Set cBar = Application.CommandBars("Worksheet Menu Bar")

On Error Resume Next
cBar.Controls("&Transactions").Delete
cBar.Controls("&List").Delete
On Error GoTo 0

End Sub

Sub ListAcctDetails()
MsgBox "Hi from listacctdetails"
End Sub
Sub ListIncExp()
MsgBox "Hi from listincexp"
End Sub
Sub ListBalSheet()
MsgBox "Hi from listbalsheet"
End Sub
Sub TranRecord()
MsgBox "Hi from tranrecord"
End Sub
Sub TranListAcc()
MsgBox "Hi from tranlistacc"
End Sub
Sub TranListByMonth()
MsgBox "Hi from tranlistbymonth"
End Sub

If you're looking for nice icons:

John Walkenbach has a FaceId identifier program at:
http://j-walk.com/ss/excel/tips/tip67.htm

Jim Rech has one at:
http://www.oaltd.co.uk/MVP/MVPPage.asp#JimRech
 
S

Stuart Grant

I'm really extremely grateful to you for taking the time and trouble to
write all this code for me. I think I understand it although I've got to do
some research in the Help Menu for things like msoControlPopup,
msoControlButton and FaceID. The only thing I noticed as perhaps not needed
was your "Dim SubMenu As CommandBarControl" since I don't think you used
SubMenu later.

Anyway I will paste the code into a module, delete the code of mine, which I
don't need, and run it. I have no doubt it will work.

Thank you again very much indeed
Stuart


----- Original Message -----
From: "Dave Peterson" <[email protected]>
Newsgroups: microsoft.public.excel
Sent: Tuesday, February 14, 2006 3:44 PM
Subject: Re: Assigning a macro to a Menu Bar option

The menubar is still supported in excel, but was superseded by commandbars
in
xl97 (IIRC).

Maybe something like this would help. It creates the items and assigns
the
macros when the workbook opens (using auto_open). It cleans up after
itself
when the workbook closes (using auto_close).

Option Explicit
Sub auto_open()

Dim cBar As CommandBar
Dim ListMenu As CommandBarControl
Dim SubMenu As CommandBarControl
Dim TransactionsMenu As CommandBarControl

Call RemoveMenus

Set cBar = Application.CommandBars("Worksheet Menu Bar")
Click to expand...

etc., etc.
 
D

Dave Peterson

Yep.

I was going to use:

set submenu = ListMenu.Controls.Add(Type:=msoControlButton, _
temporary:=True)
submenu.Caption = "&Account Details"
subment.OnAction = "'" & ThisWorkbook.Name & "'!ListAcctDetails"
submenu.FaceId = 103

instead of:

With ListMenu.Controls.Add(Type:=msoControlButton, temporary:=True)
.Caption = "&Account Details"
.OnAction = "'" & ThisWorkbook.Name & "'!ListAcctDetails"
.FaceId = 103
End With

But changed my mind and forgot to delete the dim statement.
 
S

Stuart Grant

Dave

I am sorry but I am having all sorts of trouble with your code. I pasted it
in a module but the first time the auto_open module tried to run it stopped
at
Dim cBar As Command Bar
with the error message "User-defined type not defined", which does not make
any sense at all.

I noticed in my own previous effort, which I have now commented out, that I
had started with "Dim bar As Object" before setting it. I tried changing
your line to Dim cBar as Object and that ran but choked on Dim ListMenu As
CommandBarControl with the same message, then on Dim TransactionsMenu As
CommandBarControl. I altered these to Object. It then runs the code,
including the Call to RemoveMenus ( where I had also altered the Dim
statement) but then choked on
Set ListMenu = cBar.Controls.Add(Type:=msoControlPopup,
before:=.Controls.Count, temporary:=True) with msoControlPopup highlighted
and the error message "Variable not defined", which again does not make
sense. I pasted the code from your message, so there are no typos and I
have also gone over it carefully and found nothing.

Have you any idea of what is wrong ?. I am using WindowsXP and Excel 2003.
 
D

Dave Peterson

Dim cBar As Command Bar
commandbar is one word.

And make sure you put the procedure in a General module--not behind a worksheet
and not behind thisWorkbook.
 
S

Stuart Grant

Sorry. The typo was in my message not in the code which has CommandBar as
one word. The code is in a general module - Module 1. I wondered if it
should be in thisWorkbook and actually moved it there when I ran into
trouble but have moved it back. Same errors.

Stuart
 
S

Stuart Grant

Yes I am quite sure. Checked again. ThisWorkbook module is empty. The
Auto_open Sub is in module 1.

Could I be missing a library or reference ? I don't think so but why would
I get this "Variable not defined" error for mso.ControlPopup ?

Stuart
 
D

Dave Peterson

msoControlPopup
not
mso.ControlPopup

You may want to copy and paste into a new workbook's project (from the original
post) once more.
 
S

Stuart Grant

I'm sorry. I'm sorry. The mistake was once more in the message NOT in the
code, which I did indeed paste from your original proposal. In the code it
is definitely msoControlPopup.

Stuart

----- Original Message -----
From: "Dave Peterson" <[email protected]>
Newsgroups: microsoft.public.excel
Sent: Thursday, February 16, 2006 3:36 PM
Subject: Re: Assigning a macro to a Menu Bar option
 
D

Dave Peterson

I still think it would be a good idea to start a new workbook and test it out in
that project.

I don't have a guess at what could be wrong.
 
S

Stuart Grant

Dave

I did what you said. I opened a new workbook. I inserted a module. I cut
and pasted the code from my workbook to the new one. I closed the new
workbook and reopened it. Your code works perfectly. The new menu items
were created and the OnAction opened the right subs.

What on earth can have been wrong ! As I said, I copied the code, not
directly from your message but straight from my workbook with cut and paste.
There are no typos. The code is exactly the same in both books, yet in the
new empty workbook it works perfectly. In the other it still gives the
"User-defined type not defined" error message". I'm baffled.

The only thing now seems to be to try to copy the 15 large sheets from the
original to the new one, plus the existing subs, and see if everything else
still works.

I'm very sorry that you had to spend so much time trying to help me. Thanks
a lot

Stuart
 
S

Stuart Grant

I found it.
I compared the Reference List for my workbook and the new empty one. In the
empty one were Microsoft Excel Object Library 11.0 AND Microsoft Office
Object Library 11.0. In my original workbook was only the Excel Object
Library 11.0. I managed to find the Office Object Library 11.0, and added
it my original project. Bingo the auto_open sub works !

Why it was missing in my original project yet appeared automatically when I
opened a new workbook, I shall never know.

I apologise again for taking up so much of your time !

Stuart
 
D

Dave Peterson

Not a problem. Glad you found the solution.

Stuart said:
I found it.
I compared the Reference List for my workbook and the new empty one. In the
empty one were Microsoft Excel Object Library 11.0 AND Microsoft Office
Object Library 11.0. In my original workbook was only the Excel Object
Library 11.0. I managed to find the Office Object Library 11.0, and added
it my original project. Bingo the auto_open sub works !

Why it was missing in my original project yet appeared automatically when I
opened a new workbook, I shall never know.

I apologise again for taking up so much of your time !

Stuart
Click to expand...
 
Top