Passing variables from userform to normal module based on command button clicked

[vanhunk]

Passing values of variables from a userform to the main calling code in a normal module based on which command button on the form was clicked:

I have a userform that has a message and 2 buttons, an OK button and a Cancel button.

How it is supposed to work is as follows:
When the main code in a normal module is executed, the form must open. If the OK button is clicked, the action must be referred back to the main code and some further action performed, based on the fact that the OK button was clicked. If the Cancel button was clicked the action must again be referred back to the main code and some other action performed, again based on the fact that the Cancel button was clicked.

I would for example thus like to have the values of bOK and bCancel (as below) sent to the main code, but without declaring them as public variables (because I would like to use the same variables for other forms and not cause confusion)

The form also has a timer (as below) and when the allowed interaction period has expired the code must behave exactly the same as if bOK has been clicked. In other words it has to revert back to the main code and perform some action as if the OK button has been clicked.


I have the following code in the userform module "frmEmailAlert" as attached:

Option Explicit
Private bOK As Boolean
Private bCancel As Boolean

Private Sub cmdCancel_Click()
    bCancel = True
End Sub

Private Sub UserForm_Activate()
    Dim dTimer As Double

    dTimer = Timer
    
    Do While Timer < dTimer + 5 And Not bOK And Not bCancel
        DoEvents
    Loop
    
    Unload Me
End Sub

Private Sub cmdOK_Click()
    bOK = True
End Sub

The following code is in a regular module "MainCodeModule" as attached:

Option Explicit
Sub MainCode()
  frmEmailAlert.Show vbModeless

End Sub

Regards,
vanhunk

[Aflatoon]

Assuming the form doesn't have to be modeless, you can just make the variables Public (they will be members of the class so can only be accessed through that form) and change the code to hide the form instead of Unloading it. You can then use:

Sub MainCode()    frmEmailAlert.Show vbModeless
    If frmEmailAlert.bOK Then
        ' do something
    End If
End Sub

I assume the returns are only OK or Cancel, so you only really need to test bOK.

[vanhunk]

@Aflatoon:
Thank you very much, I changed the code accordingly and it does what I intended it to (see, code below).
1) Why doesn't it work if the form is modeless?
2) If modeless is a requirement, how will you adapt the code?

I have the following code in the userform module "frmEmailAlert" as attached:

Option Explicit
Public bOK As Boolean
Public bCancel As Boolean

Private Sub cmdCancel_Click()
    bCancel = True
End Sub

Private Sub UserForm_Activate()
    Dim dTimer As Double

    dTimer = Timer
    
    Do While Timer < dTimer + 5 And Not bOK And Not bCancel
        DoEvents
    Loop
    
    If bOK = False And bCancel = False Then
    bOK = True
    End If
    
    Me.Hide
End Sub

Private Sub cmdOK_Click()
    bOK = True
End Sub

The following code is in a regular module "MainCodeModule" as attached:

Option Explicit
Sub MainCode()
  frmEmailAlert.Show
    If frmEmailAlert.bOK Then
         MsgBox "OK Selected"
    End If
    If frmEmailAlert.bCancel Then
         MsgBox "Cancel Selected"
    End If
    Unload frmEmailAlert
    MsgBox "You are now here"
End Sub

Regards,
vanhunk

[Paul_Hossler]

If you want to avoid public variables, you can have the user form module return the result as a function ('AskAboutEmail' in this example)

Main standard module

Option Explicit

Sub MainCode()
    Dim Ans As VbMsgBoxResult
    Ans = frmEmailAlert.AskAboutEmail
  
    If Ans = vbCancel Then
        MsgBox "You wanted to cancel"
    ElseIf Ans = vbOK Then
        MsgBox "Email was sent"
    End If
  
End Sub

and in the UF module

Option Explicit

Private bOK As Boolean
Private bCancel As Boolean
'vbOK       1 OK
'vbCancel   2 Cancel
'vbAbort    3 Abort
'vbRetry    4 Retry
'vbIgnore   5 Ignore
'vbYes      6 Yes
'vbNo       7 No


Public Function AskAboutEmail() As VbMsgBoxResult
    
    bOK = False
    bCancel = False
    Load Me
    Me.Show

    If bOK Then
        AskAboutEmail = vbOK
    ElseIf bCancel Then
        AskAboutEmail = vbCancel
    Else
        AskAboutEmail = vbCancel
    End If
End Function


Private Sub cmdOK_Click()
    bOK = True
    Unload Me
End Sub


Private Sub cmdCancel_Click()
    bCancel = True
    Unload Me
End Sub


Private Sub UserForm_Activate()
    Dim dTimer As Double
    dTimer = Timer
    Do While Timer < dTimer + 5 And Not bOK And Not bCancel
        DoEvents
    Loop
    Call cmdOK_Click
End Sub

[vanhunk]

@Paul_Hossler:
Great stuff, thank you very much!

@Paul_Hossler & @Aflatoon:
Once again thanks to all the excellent feedback!
Best Regards,
vanhunk

[Paul_Hossler]

The UserForm.Function I learned from one of the more experienced people here (Sorry, but I forgot who)

I like it because it keeps a lot of the 'common code' like the .Show etc. out of the standard module

Of course you can pass values to the UF function to pass to Controls or to control processing

Public Function AskAboutEmail(sName as String, Dept as String, CopyMgr as Boolean, CaptionForForm as String) As VbMsgBoxResult

for example

[Aflatoon]

Just as an FYI, there's no point to the

Load Me

line in the form code - the code couldn't be running if the form weren't already loaded.

[Paul_Hossler]

True

I was a little sloppy copy/pasting