VBA userform listbox right click menu?-CodePudding

I'm creating a userform in excel. I've created the form, I've populated it with data using a Listbox that reads from a range in excel.

Now 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 form there. Is this possible? I had assumed it was so I was planning on:

Creating a custom popup menu
Working on setting up some sort of event in the userform on right click
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

This is my code currently, I created a new userform and just pasted the code verbatim from MS's website. When I run the code 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)

I'm at a loss on what the error is. Am I on the wrong track here? Am I just doing this whole thing completely wrong?

CodePudding user response：

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)