right click menu, userform show

[natanz]

i have written a little code to add some new items to the "cells" right
click menu. the only way i could figure out to use a right click menu
was to call a second sub that opens the userform. here's what i am
doing now:

With Application.CommandBars("Cell").Controls
With .Add(temporary:=True)
.Caption = "Respond"
.OnAction = "module1.ShowForm"
.Tag = cControlTag
End With

 

[Kris]

Sub ShowForm(FormName As UserForm)
FormName.Show
End Sub

but that doesn't work. Can anyone help?

The funny thing is that your userform1 doesn't inherit from UserForm
type and UserForm itself doesn't have Show method

Sub ShowForm(FormName As Object) should work.

 

[Tom Ogilvy]

Kris had a good idea, but unfortunately it won't work.

Here is a tested solution that does work:

Sub ABCD()

cControlTag = "ABC"
With Application.CommandBars("Cell").Controls
With .Add(temporary:=True)
.Caption = "Log New RFI(s)"
.OnAction = "'ShowForm ""LogInNew""'"
'This is crazy syntax but this is what it takes to call a sub with
'arguments using .onAction
.Tag = cControlTag
.BeginGroup = True
End With
End With

End Sub

Sub ShowForm(s As String)
VBA.UserForms.Add(s).Show
End Sub

 

[natanz]

thanks for that. I had actually come up with the second solution
already and was about to post it for the edification of all.
Unfortunately, i don't really understand it, but i do know it works.
that just makes it harder to remember every time i come up against the
same problem.