Userform as custom msgbox

[brittonsm]

When I build a userform with yes and no buttons - how do I put the
selected values back into the calling code?

e.g. - No - Exit Sub and Yes - Continue code...

-Steve

 

[Vergel Adriano]

One way could be to use a public variable.

For example, paste this code in a userform with 2 buttons, cmdYes and cmdNo.

Public vbReply As VbMsgBoxResult

Private Sub cmdNo_Click()
vbReply = vbNo
Me.Hide
End Sub
Private Sub cmdYes_Click()
vbReply = vbYes
Me.Hide
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then
MsgBox "Please click Yes or No"
Cancel = True
End If
End Sub



The to test, use something like this:

Sub test()
UserForm1.Show
If UserForm1.vbReply = vbYes Then
MsgBox "User clicked Yes"
Else
MsgBox "User clicked No"
End If

Unload UserForm1
End Sub

 

[JLGWhiz]

Your question is a little ambiguous, but I will try to answer what I think
you are asking.

When you use a yes/no, true/ false or 1/0 value on a UserForm with the form
visible, if either option says exit sub from the UserForm code module, the
program reverts back to the calling code. Now, the calling code could be in
a sheet module, a VBA module (Module1) or conceivably in the UserForm
_Ininialize code if the option is attached to one of the UserForm controls.
In any event, to get back to the main procedure, you should end sub in both
options of your yes/no code and be
sure to Unload UserForm# (# means appropriate number) if you have no further
use for any of the control values that are on the form.

Illustration:

VBA Module contains Main Sub
UserForm Initializes ListBox Control
ListBox Control contains Option code with MsgBox Yes/No option
If MsgBox = No > Exit Sub
Else
MsgBox = Yes Then Do Something
End Sub

When the ListBox code has executed it will end the sub whether the option is
yes or no. VBA will then query the next step of the last procedure that did
not execute the End Sub command. In this illustration that would be the main
procedure because the initialize procedure completed after it set up the
ListBox. So the code automatically goes back to the main procedure to the
next unexecuted step and continues if the UserForm is Unloaded or Closed.

 

[RB Smissaert]

If you are interested in a custom msgbox with lots of useful options then
have a look here:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=65065&lngWId=1

It is run as function and will return the caption of the pressed button.

RBS