title: Excel VBA Userform Listbox Dynamic Context Menu Using .OnAction Method
body: <p>I have built a dynamic context menu for a <code>Userform</code> <code>Listbox</code>.</p>
  
  <p>Within this <code>Listbox</code> are a series files. My aim is that when you right click on a file, a context menu pops up with a list of folder locations. Left clicking on one of these folder locations copies the file to that location.</p>
  
  <p><em>I will be using the</em> <code>.CopyFile(Location, Destination, [Overwrite])</code> <em>method to do this.</em></p>
  
  <p>I'm having difficulty dynamically assigning <code>.OnAction</code> events for each <code>Item</code> added.</p>
  
  <p><code>Userform</code> Module Code</p>
  
  <pre><code>Option Explicit
  Private Const mCONTEXT_MENU_NAME = "myRightClickListbox"
  Private m_clsContextMenu As CContextMenu
  
  'Function mySendTo(fName As String)
      'MsgBox fName
  'End Function
  
  Sub mySendTo(fName As String)
      MsgBox fName
  End Sub
  
  Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  Dim myString As String: myString = "C:\myFolder\"
  Dim FolderName As String: FolderName = Dir("C:\myFolder\", vbDirectory)    
  
  If Button = 2 Then
          '*\\Listbox right click context menu
          On Error Resume Next
          Application.CommandBars(mCONTEXT_MENU_NAME).Delete 'remove any previous instance
          On Error GoTo 0
  
          Set m_clsContextMenu = New CContextMenu
  
          With CommandBars.Add(mCONTEXT_MENU_NAME, Position:=msoBarPopup)
              With .Controls.Add(Type:=msoControlPopup, before:=1, temporary:=True)
                  .Caption = "Send to"
  
                  i = 0
                  Do While FolderName &lt;&gt; ""
                      If FolderName &lt;&gt; "." And FolderName &lt;&gt; ".." Then
                          If (GetAttr(myString &amp; FolderName) And vbDirectory) = vbDirectory Then
                              i = i + 1
                              With .Controls.Add(Type:=msoControlButton, before:=i, temporary:=True)
                                  .FaceId = 23
                                  .Caption = FolderName
                                  .Tag = "t" &amp; FolderName
                                  .OnAction = "'mySendTo " &amp; FolderName &amp; "'"
                                  '.OnAction = "=mySendTo(" &amp; FolderName &amp; ")"
                                  '.Parameter = FolderName
                              End With
                          End If
                      End If
                      FolderName = Dir()
                  Loop
              End With
  
              Set m_clsContextMenu.LBox = Me.ListBox1
          End With
          '*//
      End If
  End Sub
  </code></pre>
  
  <p><code>Class</code> Module Code <code>CContextMenu</code></p>
  
  <pre><code>Public LBox As MSForms.ListBox
  </code></pre>
  
  <p>The code above successfully creates a right click activated context menu for a <code>Userform</code> <code>Listbox</code> which consists of a sub-menu <code>Type:=msoControlPopup</code> containing <code>Items</code> for each folder within the specified <code>FolderName</code> directory.</p>
  
  <p>I am trying to dynamically assign <code>.OnAction</code> events for each <code>Item</code> created to call the <code>mySendTo</code> <code>Sub</code> or <code>Function</code>. I've been told that you may only call <code>Functions</code> by name in this manner and that calling a <code>Sub</code> with it's own parameters will fail. None the less I have tried both and neither seem to work. Although both trigger an <code>Error: 400</code> which means Excel is trying to call the event.</p>
  
  <p>Both events just trigger a <code>MsgBox</code> displaying the parameter <code>String</code> (I have done this for simplicity until I know the code runs correctly).</p>
  
  <p>What <strong>is</strong> important is that when each <code>Item</code> in the sub-menu is clicked, it triggers code that references that specific <code>Item.Caption</code> text - in this case, the sub-folder name within the <code>FolderName</code> directory (the location of itself).</p>
  
  <p>I intend to copy files from the <code>Listbox</code> to the new destination folder indicated by that of the context sub-menu <code>Item</code>.</p>
  
  <p>I know that I'm close with my <code>.OnAction</code> syntax but whether it's because I'm mis-using my <code>Sub</code> / <code>Function</code> with parameter call events, or because I'm also trying to dynamically assign the <code>.OnAction</code> event to an already dynamically created context sub-menu <code>Item</code>, I just can't for the life of me figure it out.</p>
  
  <p>If you paste the above code into a blank <code>Userform</code> module and add a <code>Listbox</code> named "ListBox1", you should have a working right click activated context menu with a sub-menu.</p>
  
  <p>If you try and click one of these <code>Items</code> you should also receive the <code>Error: 400</code>.</p>
  
  <p>Any help on how I can pass a dynamic <code>Sub</code> or <code>Function</code> to each <code>Item</code> with a parameter being it's own <code>.Caption</code> will be <strong>much</strong> appreciated, and once again thank you for your time.</p>
  
  <p>Mr. J</p>