How to pass values from a userform to a standard module?

  • Thread starter Thread starter TBA
  • Start date Start date
T

TBA

Excel 2000
Windows 2k Pro

I need to know how I can pass values from a userform to a standard module
and vice versa.

I have a Workbook that has a custom menu from which the user can choose a
"Run Setup" option. Choosing this loads and shows a userform. One of the
controls on the userform calls a "Get_Path_and_Filename" subroutine that is
in a standard module. I'd like to return the path and file name string from
the standard module to a text box on the userform.

Should I move this subroutine to the userform module? Perhaps it's a matter
of where I declare variables? Kinda scratching my head on this one, any
suggestions appreciated.

-gk-
 
-gk-

If you make your variable public at the top of a standard
module, you should be able to retrieve it from anywhere
(including the UserForm)

John
 
As another way you can use an argument to pass values to the subroutine
that is in a standard module.


Code:
--------------------

'Userform Module
Private Sub CommandButton1_Click()
Get_Path_and_Filename Me.TextBox1.Text
End Sub

'Standard Module
Sub Get_Path_and_Filename(Optional arg As String)
MsgBox arg & " has been passed"
End Sub

--------------------
 
Alternatively, Load the userform, populate it however you want to while
still in the main module, then show it. The event that ends the user's
interaction with the UserForm should only hide it, not unload it, so the
main module can unpopulate it. Then the main module unloads the form.

' in the main sub
Load UserForm1
With Userform1
.TextBox1.Text = "Initial Text"
.Show
' now the user is in the userform

' now the user returns from the userform
TextValue = .TextBox1.Text
End With
Unload UserForm1

' in the userform
' change all instances of:
Unload Me
' to this:
Me.Hide

- Jon
 
Back
Top