One Userform for multiple Macros

[Mamue]

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]

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
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Mamue]

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

 

[Jay Freedman]

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
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Mamue]

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
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.