Menus on Userforms
There is no built in menu control in VBA like there is in VB 6.0. If you want menus on your userform, you have to get a little tricky. I use Label controls and their click events to show Popup commandbars to simulate a menu structure.
This example creates one menu, the File menu, with two controls. In the Initialze event, I create the Popup commandbar and add controls to it. Then in the Label’s click event, I call the ShowPopup method. Here’s the code behind the form.
Const sFileMenu As String = “ufFile”
Private Sub lblFileMenu_Click()
‘show the popup
Application.CommandBars(sFileMenu).ShowPopup
End Sub
Private Sub UserForm_Initialize()
Dim cb As CommandBar
‘create the popup
Set cb = Application.CommandBars.Add(sFileMenu, msoBarPopup)
With cb.Controls.Add(msoControlButton) ‘create a control
.Caption = “Recalculate”
.Style = msoButtonCaption
.OnAction = “DoRecalc” ‘in a standard module
End With
With cb.Controls.Add(msoControlButton) ‘create a control
.Caption = “Exit”
.Style = msoButtonCaption
.OnAction = “ExitForm” ‘in a standard module
End With
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
‘delete the popup
On Error Resume Next
Application.CommandBars(sFileMenu).Delete
On Error GoTo 0
End Sub
There are a couple of limitations with this method. You can’t have shortcut keys to your menus because the position of the popup is dependent on the position of the mouse. I haven’t attempted to try to get the popup to show in the normal menu position, but I think it would take some API calls. If I figure it out, I’ll blog about it, but you can save me some time and send me the code if you like. The other, related, problem is that the menu doesn’t quite look like a menu because of where the popup is shown.
If I had to do a lot of menu items, I would probably try some sort of table driven approach so I wouldn’t have to code all those commandbars and controls. But for just a few, I do it right in the Initialize event.
Re: Positioning the menu
With the control positioned at .Left=10, .Top = 20, this looks ok for me (XL04):
Private Sub lblFileMenu_Click()
‘show the popup
Dim a As Long
Dim b As Long
a = Me.Left + 10
b = Me.Top + 60
cb.ShowPopup x:=a, y:=b
End Sub
John: That puts it up and to the left of the userform for me (xl2000). The more my userform is positioned to the lower right, the further away the popup gets. Chalk one up for the Mac, I guess.
How bizarre…!
Does anyone know why the following line gives me Error 5: Invalid procedure call or argument? I’m using Dick’s code verbatim, prior to trying a few of my own embellishments. It will work the first time, then just completely crap out, both in Excel 2000 and XP. Then after about the fourth error, it just crashes Excel. I can’t find anything wrong with it.
Set cb = Application.CommandBars.Add(sFileMenu, msoBarPopup)
Very useful stuff! however can you help me out in the question wich adjustments i need to undertake to run this in 97 excel vba.
The easiest way to make it work in Excel 97 is to upgrade Excel. Joking aside, however, there is no code in the example that isn’t part of Excel 97′s VBA. (And no, I didn’t test it, but I did look in 97′s VBE Object Browser.)
I did learn why it crashed before, a great big D’oh! Whenever you create a command bar, you should first make sure it doesn’t already exist:
Application.CommandBars(sFILEMENU).Delete
On Error GoTo 0
For positioning the form in Windows, you need to convert the form’s position between pixels and points. This one works nicely for me:
‘show the popup
Dim a As Single
Dim b As Single
Dim dh As Single
Dim dw As Single
dw = (Me.Width – Me.InsideWidth) / 2
dh = Me.Height – Me.InsideHeight – dw
a = 4 / 3 * (Me.Left + lblFileMenu.Left + dw)
b = 4 / 3 * (Me.Top + lblFileMenu.Top + lblFileMenu.Height + dh)
Application.CommandBars(sFILEMENU).ShowPopup X:=a, Y:=b
End Sub
- Jon
By the way, Lars, I just tested it in 97, and it works just fine. (I stole the old PC from the kids because one client insists his project get done in 97. When you open a file in 97 that was created in a later version, you sometimes get a message that it was created in a newer version. Never mind that the file structure is identical.)
- Jon
Jon,
you are right! I try it on my again private excel 97 version and it work very well. Good idea to replace the popup – looks very well.
Cheers
Lars
hi guys,
Dear Jon’s (is this what they mean by a dear John leter!!!!)
I tryed both, neirther gave satifactory results. Postion of the popup depened on the form postion it’s pstion relavtive to the edge of the screen
I have been working on a method using a Combo box – fixed the posion issue, but it don’t look a good, will keep looking into it.
Cheers
I looked at this agian quickly this morning, and i may be mistaken but, i think you would have to postion the menu, based on the postion of active window and the froms relative postion. If the window was max’ed, or min’ed it’s postion would need to change respectly. Chip Pearson has some good code that moves a form about with the results we are looking for – but it’s a lot of work!
My altertaive is to use a combo box. – i’ll put it on my site later:
http://www.freewebs.com/methodsinexcel/articles.html