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 / Word / Programming / July 2007

Tip: Looking for answers? Try searching our database.

One Userform for multiple Macros

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Mamue - 04 Jul 2007 14:42 GMT
Hi,

i need the same userform in multiple macros of my vba project without
copying it. calling the userform is not the problem, but i don't know
who to manage it, that the return values are sent to the calling
macro.
For example, i have to macros

sub macro_add()
  userform.show
end sub

sub macro_multiply()
  userform.show
end sub

and one userform where the user enters two values.
after pushing the ok button, the userform has to know where the result
has to be sent.

do you have an idea?

greetings mamue
Jay Freedman - 04 Jul 2007 17:46 GMT
Ah, clearly you've never run into Malcolm Smith, who used to post here
a lot. His favorite topic was the so-called "magic form", whose major
drawback you've just seen.

The problem is that the userform that you design in VBA is really a
"class", or a template for making multiple forms that all have the
same behavior. It's unfortunate that VBA also allows you to invoke an
instance of the class itself just by calling its .Show method. When
you do that from several different macros, though, they all get a
reference to the _same_ instance.

In each of your macros, construct a local object whose type is the
userform class. Each object will refer to a _different_ instance, and
will return its values to the macro that owns it. Here's the sort of
code you need:

Sub macro1()
  Dim localString As String
  Dim UF1 As UserForm1
  Set UF1 = New UserForm1
  With UF1
     ' optionally, set any starting variables in the userform
     .GlobalVar1 = "starting value"

     .Show  ' runs the specific instance UF1 until it hides

     ' retrieve results and send to document
     localString = .TextBox1.Text
     If Len(Trim(localString)) > 0 Then
        ActiveDocument.Bookmarks("bk1").Range.Text _
           = localString
     End If
  End With

  Set UF1 = Nothing  ' free its memory
End Sub

In the userform itself, instead of calling the Unload method to end
the userform, call Me.Hide, which returns control to the calling macro
but doesn't remove it from memory (that's why you set the userform
object to Nothing at the end of the macro).

--
Regards,
Jay Freedman
Microsoft Word MVP        FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

>Hi,
>
[quoted text clipped - 19 lines]
>
>greetings mamue
Mamue - 05 Jul 2007 07:17 GMT
Hi Jay,

thanks a lot. I didn't searched for "Magic Form" ;-)
how differs my macro if button "OK" or "Cancel" was pressed? do i have
to check each button like:
if(buttonOK.isPressed) then .....

is there such a method?

greetings mamue

> Ah, clearly you've never run into Malcolm Smith, who used to post here
> a lot. His favorite topic was the so-called "magic form", whose major
[quoted text clipped - 71 lines]
>
> >greetings mamue
Jay Freedman - 05 Jul 2007 15:52 GMT
No, there isn't anything like buttonOK.isPressed. Instead, there are
"event procedures" that run when buttons are clicked or other things
happen in the userform. For example, if you give the OK button the
name cmdOK, then there will be a cmdOK_Click event procedure that you
can choose from the dropdowns at the top of the code window. That
procedure will be called each time the user clicks the cmdOK button.
Similarly, there will be a cmdCancel_Click procedure that runs when
the cmdCancel button is clicked.

To distinguish between OK and Cancel, you can do something like this.
Declare a boolean Public variable at the top of the userform code,
which makes it a property of the userform that can be referenced in
the calling macro. It will start with the value False, and will be
changed to True only if the user clicks the Cancel button. Here's some
very simplified code:

In the calling macro:

Sub Test()
   Dim UF1 As UserForm1
   Set UF1 = New UserForm1
   With UF1
       .UserCanceled = False
       .Show
       If .UserCanceled Then
           MsgBox "You pressed Cancel"
           Exit Sub
       End If
       
       MsgBox "You pressed OK"
       ' do more processing
   End With
End Sub

In the userform:

Public UserCanceled As Boolean

Private Sub cmdCancel_Click()
   UserCanceled = True
   Me.Hide
End Sub

Private Sub cmdOK_Click()
   ' do any processing
   ' UserCanceled is still False
   Me.Hide
End Sub

--
Regards,
Jay Freedman
Microsoft Word MVP        FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

>Hi Jay,
>
[quoted text clipped - 82 lines]
>>
>> >greetings mamue
Mamue - 09 Jul 2007 08:21 GMT
Hi Jay,

Thanks a lot ;-)

> No, there isn't anything like buttonOK.isPressed. Instead, there are
> "event procedures" that run when buttons are clicked or other things
[quoted text clipped - 141 lines]
>
> >> >greetings mamue
 
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.