Using vbYesNo in conjuction with Userform

[Corey]

~~~~~~~~~~ Start CODE~~~~~~~~~~~~~~~~~
If ComboBox6.Value + ComboBox8.Value + ComboBox10.Value + ComboBox12.Value + ComboBox14.Value < TextBox47 Then
MsgBox "You have Not Used a Combination long enough" & vbCrLf & vbCrLf & vbTab & " to make up the Final Requirment.", vbYesNo, " ...."
End If
Unload Me
Sheets("Main").Select
Range("A1").Activate
End Sub
~~~~~~~~~~End Code ~~~~~~~~~~~~~~~~~~~
The above code is the last section of a userform i am creating.
What i am trying to do is prompt the user in case the values in the comboboxes 6-14,
DO NOT add up to the value placed in textbox47.

I want a VbYesNo prompt for the user to either continue with a Yes or
Modify the userform with a No.

But how can i get vbYesNo to Carry on with w Yes through the remaining code,
OR
Exit the vbYesNo and KEEP the Userform OPEN and then Activate the value in textbox47 for modifying,
WITHOUT exiting or unloading the values from the userform.
I am sure it is a simple placement of coding but it has eluded me to date.

Corey....

 

[Tom Ogilvy]

' at the top of the module above any code
Public ans as long


then in a sub in that module:

ans = MsgBox( "You have Not Used a Combination long enough" & vbCrLf &
vbCrLf & vbTab & " to make up the Final Requirment.", vbYesNo, " ....")
' following code is for illustration only to demonstrate possible usage
if ans = vbYes then
msgbox "Yes"
elseif ans = vbNo then
msgbox "No"
else
msgbox "Not possible"
end if

--
Regards,
Tmo Ogilvy


~~~~~~~~~~ Start CODE~~~~~~~~~~~~~~~~~
If ComboBox6.Value + ComboBox8.Value + ComboBox10.Value + ComboBox12.Value +
ComboBox14.Value < TextBox47 Then
MsgBox "You have Not Used a Combination long enough" & vbCrLf & vbCrLf &
vbTab & " to make up the Final Requirment.", vbYesNo, " ...."
End If
Unload Me
Sheets("Main").Select
Range("A1").Activate
End Sub
~~~~~~~~~~End Code ~~~~~~~~~~~~~~~~~~~
The above code is the last section of a userform i am creating.
What i am trying to do is prompt the user in case the values in the
comboboxes 6-14,
DO NOT add up to the value placed in textbox47.

I want a VbYesNo prompt for the user to either continue with a Yes or
Modify the userform with a No.

But how can i get vbYesNo to Carry on with w Yes through the remaining code,
OR
Exit the vbYesNo and KEEP the Userform OPEN and then Activate the value in
textbox47 for modifying,
WITHOUT exiting or unloading the values from the userform.
I am sure it is a simple placement of coding but it has eluded me to date.

Corey....

 

[Corey]

Thanks for the reply Tom.

I am running the code from a commandbutton.

It starts with:
Private Sub CommandButton1_Click()

I get an error if i place the :
Public ans as long
under the Private Sub line.

The code in not in a module so i do not know where excactly to place it.

Corey.....

 

[Corey]

Change the Public ans as long
to
dim ans as long
to get it to work.

Thanks Tom.

Corey....

 

[Tom Ogilvy]

It works fine if you put it at the very top of the module that contains your
event code as I previously instructed. (all code is contained in a module).

If you only need the information within the click event, then declaring it
within the event will work as well (using DIM as you state).

It was unclear what visibility you wanted.