excelvba

Create userform listbox right click menu


I created a userform in Excel. I populated it with data using a Listbox that reads from a range in Excel.

I'm trying to add the ability to right click on a row in the Listbox and then perform an action on that line of data from there. Is this possible?

I was planning on:

  1. Creating a custom popup menu
  2. Working on setting up some sort of event in the userform on right click
  3. Everything else.

I'm stuck on step 1 above. I started by trying to create a custom right-click menu by using the code from Microsoft's website on "showpopup".

https://learn.microsoft.com/en-us/office/vba/api/office.commandbar.showpopup

Private Sub UserForm_Initialize()

Set myBar = CommandBars _
    .Add(Name:="Custom", Position:=msoBarPopup, Temporary:=False)
With myBar
    .Controls.Add Type:=msoControlButton, ID:=3
    .Controls.Add Type:=msoControlComboBox
End With
myBar.ShowPopup

End Sub

I created a new userform and pasted the code verbatim from MS's website.

I get this error:

Run-time error: '5': Invalid procedure call or argument

This is the line that's causing the error:

Set myBar = CommandBars _
    .Add(Name:="Custom", Position:=msoBarPopup, Temporary:=False)

Solution

  • The reason for the error is that the CommandBar already exists ... before the line that is erroring, add:

    On Error Resume Next
    CommandBars.Item("Custom").Delete
    On Error GoTo 0
    

    ... you should also really run the same lines to delete the CommandBar when your UserForm is no longer in use, maybe in the Terminate event

    ... also your code, as it stands, shows the CommandBar immediately, you likely want to move the myBar.ShowPopup line elsewhere (eg to the appropriate event handler for the ListBox)