excel vba how to pass values to form code example

Example: passing variables to userform vba

' 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