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