15 thoughts on “Menus on Userforms”

1. 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

2. 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.

3. How bizarre…!

4. 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)

5. 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.

6. 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:

       On Error Resume Next
       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:

   Private Sub lblFileMenu_Click()

       ‘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

7. 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

8. 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

9. 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

10. 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

11. I tripped across this code while looking for a way to add a commandbar popup to my own userform, but I ran into a problem I can’t explain. I can use your code verbatim and I can even change the Const variable name to what I like instead of sFileMenu. However, if I change the value of the constant (“ufFile”), I get an error on the Application.CommandBars(sFileMenu).ShowPopup.

    I’m using O365

    Empirically this suggests that there is some limitation to the Name of the command bar, perhaps a range of standard names (although the literature from MS suggests otherwise), or I need to set some other variable to use a custom name (in my case, “ufLeave”).

    I’d really like to understand why this is happening.

    My code is (roughly):

    Module 1:
    Const LeaveMenu As String = “ufLeave”

    Private Sub CommandButton1_Click()
    ‘show the popup
    Call ShowPopupMenu
    End Sub

    Private Sub ShowPopupMenu()
    Application.CommandBars(sFileMenu).ShowPopup
    End Sub

    Private Sub UserForm_Initialize()

    Dim cb As CommandBar

    ‘create the popup
    Set cb = Application.CommandBars.Add(LeaveMenu, 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

    In case you’re wondering, I use the Call sub but I have a conditional test in my ShowPopupMenu to position the popup based on certain calling criteria, but that is superfluous to this example. I also have code in the CommandButton1_Click routine that tests for certain items in a Listbox having been selected, but again this would complicate the example I’ve provided. Essentially the above code uses
    Const LeaveMenu as String = “Leave” ~ Errors
    instead of
    Const LeaveMenu as String = “ufFile” ~ Works

    In fact, Const sFileName as aString = “uffile”, also fails.

12. It doesn’t matter what I change the constant value to, I can’t make it error. I also added a commandbutton and duplicated your code, but it still works. You say “Module1” but none of your code should be outside the userform’s module.

    It sounds like you have two constants declared in two different modules. Search the whole project for ufFile and see if it’s declared somewhere you don’t expect it to be.

13. Hmmm. I’ve only set the Constant in the one location, Module 1, General, so it’s a global constant (the rest of the code is in the userform module). I even did a find on my entire project to verify and that’s the only place I have any Const variable set at all.

    However, I’ve decided to attack my requirement differently, because I discovered that the CommandBar only works if the workbook is maximized. My application leaves the workbook minimized and uses an OnTime function to periodically launch my userform. So the CommnadBar technique simply won’t achieve what I want to do. I was trying to use it because a popup menu floats on the screen and is not confined to the bounds of the current userform. The only alternative is to load a separate secondary userform from where the selection can be made.

    This triggered another problem – perhaps you can help:
    My primary userform has a Listbox in which I have a double-click event to show the second userform.
    The second userform has another Listbox with a double-click event to save the selected value in a global variable from that Listbox and unload that userform (Unload Me).
    However, I’ve found that if the second userform’s Listbox sits on top of the primary userform’s Listbox, the primary user form seems to trigger a Listbox click or change event as soon as the second userform unloads. This results in primary userform listbox selecting whatever selection happened to be directly under item I double-clicked on the second userform.

    I have tried placing DoEvents commands in all sorts of locations in my code, before and after the second userform show command and in the dohble-click routine of the second userform in an effort to clear any queued events, but nothing worked. I’ve even tried placing Cancel=True commands in my code, again with no success.

    For now, I’ve worked around this problem by forcing the primary userform’s listbox selection back to the one I double-clicked on to cause the second userform to show. This is not ideal as the selection visually bounces between the two listbox items in the primary userform, but it does work.

    For transparency, I also has some Windows API code to achieve always-on-top code in each of my primary and secondary userforms’ Activate routines, as my application is setup have the forms always on top to ensure the user can’t ignore them, forcing them to respond to the userform. I have to do this in both the primarty and secondary userforms, otherwise the second userform appears behind the primary and can’t be seen on-screen. However, I can’t see how this WindowPlacement code would affect the VBA event triggers.

    Unfortunately, I can’t share my entire code with you due to intellectual property issues and it’s large and complex, so probably wouldn’t be at all helpful anyway.

    Thanks,
    Max

14. Sorry, I’ve never heard of a double-click piercing through windows like that. It sounds like the primary is somehow getting the focus for a split microsecond (enough to feel the effects of a single click), but not enough to disrupt the double-click on the secondary. That’s pure speculation and not even an educated guess, so take it for what it’s worth.

15. Yep. Indeed it does seem like a timing problem inherent in Excel. Adding a 0.5 second delay in second form’s dblclick before the Unload Me command works around the problem. But it does seem to be an undocumented feature. It only happens if the second form’s list box selection sits directly above the first form’s list box. If I drag it away from there, there’s no issue.

    Ah well…

Posting code? Use <pre> tags for VBA and <code> tags for inline.