|
|
|
|
|
|
|
|
|
Multiple Apps
|
Userforms: Passing Variables
|
|
|
Ease of Use
|
Intermediate
|
|
Version tested with
|
2000 (Win); X (Mac)
|
|
Submitted by:
|
BlueCactus
|
|
Description:
|
Demonstration of how to pass variables to Userforms without going Public
|
|
Discussion:
|
Discussion of how to pass variables to Userforms seems to come up fairly frequently among VBA users.
The most common answer is to declare the variables using a Public statement at the module level of the calling code. This is easy, but overuse of Public statements is not recommended due to the risk of conflict between similarly named variables.
Alternatives include: 1) Placing the variable values on the worksheet, and recalling them in Userform_Initialize(). 2) Loading the form, assigning the variable values to label captions within the form (e.g., Label1.Caption = testVar), then showing the form. Labels can be made invisible (Label1.Visible = False) to make this process transparent to the user.
It occurred to me that there is a cleaner alternative. I am sure I'm not the first to think of this, but I have not yet seen it used elsewhere. The trick is simply to insert an extra Sub into the Userform code. This Sub must not be Private. Once the form is loaded in the calling code, variables can be passed to this extra Sub just like any other. See the example code and the instructions, to understand how this works.
|
|
Code:
|
instructions for use
|
' THIS CODE GOES INTO Module1
Sub TestForm()
Dim labelA As String, labelB As String
labelA = "TEST ONE"
labelB = "TEST TWO"
' MUST load the form first!
Load UserForm1
' Send the variables over to the form
Call UserForm1.FillVars(labelA, labelB)
' Now show the form
UserForm1.Show
End Sub
'-------------------------------------------------------------
' THIS CODE REPLACES ALL CODE IN UserForm1
' Dim variables up here so that they are available to the entire form
Dim str1 As String, str2 As String
Sub FillVars(ByRef s1 As String, ByRef s2 As String)
' This is the sub that collects the variables from the calling module.
' Make sure it's not marked 'Private'.
' Any form initialization that relies on external variables should be done here.
Label1.Caption = s1
Label2.Caption = s2
' s1 and s1 are not visible to other Subs in the form,
' so we'll pass their contents to str1 and str2 before leaving.
str1 = s1
str2 = s2
End Sub
Private Sub CommandButton1_Click()
Unload UserForm1
End Sub
Private Sub CommandButton2_Click()
MsgBox "You passed:" & Chr(13) & str1 & Chr(13) & str2
End Sub
Private Sub Userform_Initialize()
' Any initialization that is dependent on passed variables MUST be done in
' FillVars() and not here. Initialize() is called before the variables are passed!
CommandButton1.Caption = "Done"
CommandButton2.Caption = "Show Vars"
End Sub
|
|
How to use:
|
- (For Excel) Open a blank workbook.
- Press Alt+F11 to open the Visual Basic Editor (VBE).
- From the menus, Insert -> Module
- Copy the code above destined for Module1, and paste it into the Module1 window in VBE.
- From the menus, Insert -> UserForm
- Using the toolbox (View -> Toolbox if it's not visible):
- a. Place two 'Labels' on the UserForm
- b. Place two CommandButtons on the UserForm
- Double-click on the UserForm1 (UserForm) window to go to the UserForm1 (Code) window.
- REPLACE UserForm code in the window with the above code marked for UserForm1.
- Close the VBE by clicking on the Excel icon in the toolbar.
|
|
Test the code:
|
- Test from the menus: Tools -> Macros... -> TestForm() -> Run.
- Click 'Show Vars' to verify that variables were passed.
- Click 'Done' to finish.
- Read the comments in the code for info on how it is done!
|
|
Sample File:
|
No Attachment
|
|
Approved by sandam
|
|
This entry has been viewed 149 times.
|
|
|