PDA

View Full Version : [SOLVED] Passing variables from userform to normal module based on command button clicked



vanhunk
07-22-2015, 07:10 AM
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
07-22-2015, 08:07 AM
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
07-22-2015, 12:40 PM
@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
07-22-2015, 03:36 PM
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
07-23-2015, 12:24 AM
@Paul_Hossler:
Great stuff, thank you very much!

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

Paul_Hossler
07-23-2015, 06:15 AM
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
07-23-2015, 07:18 AM
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
07-23-2015, 06:27 PM
True

I was a little sloppy copy/pasting