Passing info from macro to UserForm

[Guest]

What is the best way to get data that I have in a macro to a UserForm? I
want a variable in MacroA to be available to UserFormA. From best practices,
I know that global variables aren't the best solution. What would be the
recommended practice to get that data in MacroA to UserFormA?

Thanks,

J

 

[Jim Cone]

UserFormA.Tag = CStr(MyVariable)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"ybazizi" <[email protected]>
wrote in message...
What is the best way to get data that I have in a macro to a UserForm? I
want a variable in MacroA to be available to UserFormA. From best practices,
I know that global variables aren't the best solution. What would be the
recommended practice to get that data in MacroA to UserFormA?
Thanks,
J

 

[Doug Glancy]

J,

One way is to create a property in the UserForm using a Let statement. The
book Professional Excel Development covers this well.

Doug

 

[Doug Glancy]

J,

Here's an example, which I developed from the book I already mentioned.

You need a UserForm1 with the following code. You'll have to name the two
CommandButtons as shown:

Private Sub UserForm_Activate()
Call initialize_controls(data_in)
End Sub

Property Let data_on_form(passed_data As String)
data_in = passed_data
End Property

Property Get data_on_form() As String
data_in = TextBox1.Value
data_on_form = data_in
End Property

Sub initialize_controls(data_in As String)
TextBox1.Value = data_in
End Sub

Private Sub OKButton_Click()
'for Closed with OK button property
OK_clicked = True
Me.Hide
End Sub

Property Get closed_with_OK()
'this property tells a calling procedure whether the OK button was clicked
closed_with_OK = OK_clicked
End Property

Private Sub CancelButton_Click()
Me.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'if closed with x it calls the Cancel routine,
'so form is hidden, not unloaded
If CloseMode = vbFormControlMenu Then Cancel = True
Call CancelButton_Click
End Sub

Then in a regular module paste this code.

Sub pass_to_uform()
Dim uform_instance As UserForm1
Dim data_to_pass As String

data_to_pass = "widget A"
Set uform_instance = UserForm1
'pass the events from the registry to the form
uform_instance.data_on_form = data_to_pass
uform_instance.Show
'only do if the user chose OK
If uform_instance.closed_with_OK Then
'fill the form events array, with those from the form
data_to_pass = uform_instance.data_on_form
End If
Unload uform_instance

MsgBox data_to_pass

End Sub

Run the code. You'll see the original value "Widget A" in the TextBox.
Change the value and click OK. The form closes and the MsgBox displays your
changed value. Again, all from PED.

hth,

 

[Doug Glancy]

Whoops, I missed some code with my paste. This goes at the very top of the
UserForm, before Private Sub UserForm_Activate():

Private data_in As String
'global variable for property whether closed with the OK button
Private OK_clicked As Boolean

hth,

Doug