Call "Property get" in another procedure-CodePudding

I have this userform in which I use a property get. It works in this way:

1.If it's clicked "Ok", the userform hides and a procedure in the module runs.

2.If nothing is selected, MsgBox appears and the userform is shown again until an option is selected

3.If it's clicked "cancel", information should be unloaded and exit from macro.

The problem is that the step three doesn't happen. When it's clicked "cancel" property get runs. Also, if user doesn't select any option twice, procedure in the module runs anyway, which it produces an error. I guess the problem is related to not to call property get inside "OK" procedure. Any suggestion how could I manage it?

In the userform

Private Sub cmdAceptar_Click()
    Me.Hide
End Sub

Private Sub cmdCerrar_Click()
    Unload Me
End Sub

Public Property Get Sup() As String

    If optSupA = True Then
        Sup = "SuplierA"
    ElseIf optSupB = True Then
        Sup = "SuplierB"
    ElseIf optSupC = True Then
        Sup = "SuplierC"
    Else
        MsgBox "A suplier should be selected", vbExclamation
        Me.Show
    End If
    
End Property

In the module

Dim result as String
Public Sub TEST()

    'Show userform modal
    frmSuplier.Show vbModal

    result = frmSuplier.Sup 
       
    'Call Calculate_prices
    Call Calculate_price(result)
    
    'now unload the form
    Unload frmSuplier
    
End Sub

CodePudding user response：

My suggestion is to move the whole IF/ELSE tree to be inside the Control_Change events for whichever pieces of the UserForm that are grabbing the value for optSupA, optSupB, optSupC. The Property Get Sup should look like Sup = SavedValue with no MsgBox or interaction with the userform controls.

Assuming there are 3 option buttons, here is how I would set that up:

Private SupValue As String
Private Sub cmdAceptar_Click()
    If Me.OptionButton1.Value Or Me.OptionButton2.Value Or Me.OptionButton3.Value Then
        Me.Hide
    Else 'None of the options are currently selected
        MsgBox "A suplier should be selected", vbExclamation
    End If
End Sub

Private Sub cmdCerrar_Click()
    Unload Me
End Sub

Public Property Get Sup() As String
    Sup = SupValue
End Property

Private Sub OptionButton1_Change()
    If Me.OptionButton1.Value Then
        Me.OptionButton2.Value = False
        Me.OptionButton3.Value = False
        SupValue = "SupplierA"
    End If
End Sub

Private Sub OptionButton2_Change()
    If Me.OptionButton2.Value Then
        Me.OptionButton1.Value = False
        Me.OptionButton3.Value = False
        SupValue = "SupplierB"
    End If
End Sub

Private Sub OptionButton3_Change()
    If Me.OptionButton3.Value Then
        Me.OptionButton1.Value = False
        Me.OptionButton2.Value = False
        SupValue = "SupplierC"
    End If
End Sub

With these changes, the cancel button Unloads the userform, erasing SupValue. So in your module, result will be blank and you can add an if/else to handle those situations like If result = "" Then Exit Sub

Unloading with the cancel button, and then calling for frmSuplier.Sup actually re-Initializes the form. So to avoid this double Initializing and double Unloading, change the cancel button to be:

Private Sub cmdCerrar_Click()
    SupValue = ""
    Me.Hide
End Sub

This way you're still getting result = "" when the user presses cancel, but you're only doing a single Load and Unload.