Passing variable from Userform to Module in VBA-CodePudding

I'm trying to pass the value of the variable "proveedor" from userform to another module. But I don't get it works. When I run "TEST Sub" appears that the "proveedor" variable wasn't defined. Any suggestion?

In the UserForm

Private Sub cmdAceptar_Click()

    If optProveedorA.Value = True Then
        proveedor = "ProveedorA"
    End If

    If optProveedorB.Value = True Then
        proveedor = "ProveedorB"
    End If

    If optProveedorC.Value = True Then
        proveedor = "ProveedorC"
    End If
    
End Sub

In the module

Public proveedor As String

Public Sub TEST()
    'Show userform
    frmProveedor.Show
    
    Debug.Print proveedor
    
End Sub

CodePudding user response：

I prefer following pattern to retrieve selected values from a userform:

Module that calls the userform:

Public Sub TEST()

    'Show userform modal
    frmProveedor.Show vbModal

    'when form is hidden - via cmd-button - we proceed in this sub
    'retrieve the selected value from the user form itself
    Debug.Print frmProveedor.proveedor  'or assign to a local variable
    
    'now unload the form
    Unload frmProveedor
    
End Sub

This is the userforms module It is responsible to return the correct value for proveedor

Option Explicit

Public Property Get proveedor() As String
If optProveedorA = True Then
    proveedor = "ProveedorA"
ElseIf optProveedorB = True Then
    proveedor = "ProveedorB"
ElseIf optProveedorC = True Then
    proveedor = "ProveedorC"
Else
    Err.Raise vbObjectError, , "No proveedor selected."
End If
End Property

Private Sub cmdAceptar_Click()
'important - we hide the form here - unload is done by the calling sub
Me.Hide
End Sub

CodePudding user response：

You need to assign global variables a value in a module. Have your userform code call a sub that does the assigning.

Userform:

Private Sub cmdAceptar_Click()

    If optProveedorA.Value = True Then
        Module1.assignproveedor "ProveedorA"
    End If

    If optProveedorB.Value = True Then
        Module1.assignproveedor "ProveedorB"
    End If

    If optProveedorC.Value = True Then
        Module1.assignproveedor "ProveedorC"
    End If
    
End Sub

Module:

Public proveedor As String

Public Sub TEST()
    'Show userform
    frmProveedor.Show
    
    Debug.Print proveedor
    
End Sub

Public Sub assignproveedor(valstr As String)
    proveedor = valstr
End Sub

Be aware that Sub Test() will hang until the userform is closed, so the debug.print will only trigger once and print the value last assigned.

If you want to avoid globals:

Module:

Public Sub TEST()
    'Show userform
    frmProveedor.Show
    
End Sub

Public Sub printOptionSelect(valstr As String)
    Debug.Print valstr
End Sub

Userform:

Private Sub cmdAceptar_Click()

    If optProveedorA.Value = True Then
        Module1.printOptionSelect "ProveedorA"
    End If

    If optProveedorB.Value = True Then
        Module1.printOptionSelect "ProveedorB"
    End If

    If optProveedorC.Value = True Then
        Module1.printOptionSelect "ProveedorC"
    End If
    
End Sub

This will trigger the print with each button click since the print is no longer reliant on the hung Test sub.