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:
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)
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)