How to get userform Cancel button to invoke Exit Sub in calling macro?

[Craig Remillard]

I have a userform that is called from inside a sub in a module of a workbook. In order to keep most of the code inside the module, the userform only sets variables in the sub. Here is the code in the sub:

'create and initialize variables
Dim Bkp,XSub as Boolean
Dim BkpSfx as String

Bkp = True
XSub = False

'call options box
BackupOptionsBox.Show

'Set main subroutine variables
Bkp = BackupOptionsBox.YesOption
BkpSfx = "_" & BackupOptionsBox.SfxBox.Value
XSub = BackupOptionsBox.CancelButton.Value

'unload backup box
Unload BackupOptionsBox
Debug.Print ("Yes=" & Bkp & ", No=" & _ BackupOptionsBox.NoOption & ", XSub=" & XSub)
'Drop out of sub if cancel button was pushed
If XSub Then
prj.FileOpenEx Name:=PrjName & PrjXtn
Exit Sub
End If

The box is has the following controls:
1. Two radio buttons, YesOption and NoOption, which toggle YesOption.Value
2. A text input field, SfxBox, for typing which is enabled when YesOption = True
3. An OK button
4. A Cancel button

I have the following event sub for CancelButton:

Private Sub CancelButton_Click()
CancelButton.Value = True
Me.Hide
End Sub

When I run the main subroutine and click the OK button, the code executes correctly (Bkp = True or False depending on the option button I selected, and XSub = False in the debug.print statement).

However, when I just click the Cancel button, XSub remains false in the debug.print statement.

Any ideas?

EggHeadCafe - Software Developer Portal of Choice
ASP Intrinsics Under COM Plus in the VB Windows 2000 ComponentASP
http://www.eggheadcafe.com/tutorial...16-2d1312d4d930/asp-intrinsics-under-com.aspx

 

[Dave Peterson]

You could add another boolean(?) variable (either public in a General module) or
a variable in the userform module that you can test. Then change that variable
in the cancelbutton_click to false and in the okbutton_click to True.

Another option would be to use the userform's tag as that variable.

Private Sub CancelButton_Click()
me.tag = False
Me.Hide
End Sub

And make it true in okbutton_click.

Then check that tag in the general procedure.

if BackupOptionsBox.tag = true then
'ok code
else
'cancel code
end if