Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / January 2006

Tip: Looking for answers? Try searching our database.

right click menu, userform show

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
natanz - 25 Jan 2006 20:08 GMT
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
       .
       .
  End With

and

  Public Sub ShowForm()
      UserForm1.Show
  End Sub

so i thought as long as i have to call a new subroutine anyway, i might
as well make it more generic, and pass the userform name as an argument
to the sub.

now my first snippet looks like this:

   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

and my second snippet looks like this so far:

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

but that doesn't work.  Can anyone help?
Kris - 26 Jan 2006 19:04 GMT
> 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 - 26 Jan 2006 20:02 GMT
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
Signature

Regards,
Tom Ogilvy

> > Sub ShowForm(FormName As UserForm)
> >     FormName.Show
[quoted text clipped - 6 lines]
>
>   Sub ShowForm(FormName As Object)  should work.
natanz - 26 Jan 2006 20:11 GMT
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.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.