ExcelBanter - msgbox answer

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - msgbox answer (https://www.excelbanter.com/excel-programming/347893-msgbox-answer.html)

Hello again!

How do you make things happen on the msgbox event? if someone says yes,
cancel, retry or any similar response nothing happens, the code just
carries on as normal!

I was rather hoping that the results of said answers might be preset!
so i assume that i need to set the answer?

I have tried something like "if msgbox = vbyes then dosomething elseif
etc etc" but having problems getting that to work so i assume thats not
the way to go about it.

Any ideas?

Duncan

Hi

Dim Response as Variant

Response = Msgbox(Text, vbYesNoCancel, Titletext)

If Response = vbYes then
do something
end if

regards
Paul

Hi Duncan.

Try something like:
'=============
Public Sub Tester001()
Dim res As Long

res = MsgBox(Prompt:="Do you want to ...", _
Buttons:=vbYesNoCancel)

If res = vbYes Then
'do something, e.g.:
MsgBox "You said yes"
ElseIf res = vbNo Then
' do domething else, e.g:
MsgBox "You said no"
Else
MsgBox "You cancelled"
End If

End Sub
'<<=============

---
Regards,
Norman

"Duncan" wrote in message
oups.com...
Hello again!

How do you make things happen on the msgbox event? if someone says yes,
cancel, retry or any similar response nothing happens, the code just
carries on as normal!

I was rather hoping that the results of said answers might be preset!
so i assume that i need to set the answer?

I have tried something like "if msgbox = vbyes then dosomething elseif
etc etc" but having problems getting that to work so i assume thats not
the way to go about it.

Any ideas?

Duncan

Yep, Brilliant. This works a treat. The only thing im slightly unsure
about now is how to get it to go back to putting in the search info
upon vbno. for some reason it shows me another msgbox that is blank?!
(ill paste my whole sub in and perhaps you might spot something, ive
probly gone arse-about-face!)

Private Sub submitint_Click()

'sets up error handler
On Error GoTo errorhandler
'disable screeen flickering
Application.ScreenUpdating = False
'set up range to search
Range("A1:A65000").Select

'sets the find option to match the userform-textbox to the range
Selection.Find(What:=Interiminput.regint.Value, LookIn:=xlValues,
SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate

'Message Box tells user what the name and reg is
MsgBox "Is this the correct Employer? " & ActiveCell.Offset(0, 2),
vbYesNo, " Confirm "
Dim Response As Variant
Response = MsgBox(Text, vbYesNo, Titletext)
If Response = vbYes Then
' submit the data to the relevant cells (working)
ActiveCell.Offset(0, 22).Range("A1").Select
ActiveCell.Value = (claimnoint)
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = (daysint)
' clear the cells for next entry
regint.Value = ""
claimnoint.Value = ""
daysint.Value = ""
MsgBox "Submitted", vbOKOnly, " Status "
Cells(1, 1).Select
Exit Sub
ElseIf Response = vbNo Then
Exit Sub
End If

'show message box if match not found
errorhandler:
MsgBox "Reg not found! Please retry", vbOKOnly, "Registration not
found"
regint.Value = ""
regint.SetFocus

End Sub

Private Sub submitint_Click()
Dim txt as String
Dim TitleText as String
'sets up error handler
On Error GoTo errorhandler
'disable screeen flickering
Application.ScreenUpdating = False
'set up range to search
Range("A1:A65000").Select

'sets the find option to match the userform-textbox to the range
Selection.Find(What:=Interiminput.regint.Value, LookIn:=xlValues,
SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate

'Message Box tells user what the name and reg is
Txt = "Is this the correct Employer? " & ActiveCell.Offset(0, 2),
TitleText = " Confirm "
Dim Response As Variant
Response = MsgBox(Txt, vbYesNo, Titletext)
If Response = vbYes Then
' submit the data to the relevant cells (working)
ActiveCell.Offset(0, 22).Range("A1").Select
ActiveCell.Value = (claimnoint)
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = (daysint)
' clear the cells for next entry
regint.Value = ""
claimnoint.Value = ""
daysint.Value = ""
MsgBox "Submitted", vbOKOnly, " Status "
Cells(1, 1).Select
Exit Sub
ElseIf Response = vbNo Then
Interiminput.regint.Value = ""
Exit Sub
End If

'show message box if match not found
errorhandler:
MsgBox "Reg not found! Please retry", vbOKOnly, "Registration not
found"
regint.Value = ""
regint.SetFocus

End Sub

--
Regards,
Tom Ogilvy

"Duncan" wrote in message
oups.com...
Yep, Brilliant. This works a treat. The only thing im slightly unsure
about now is how to get it to go back to putting in the search info
upon vbno. for some reason it shows me another msgbox that is blank?!
(ill paste my whole sub in and perhaps you might spot something, ive
probly gone arse-about-face!)

Private Sub submitint_Click()

'sets up error handler
On Error GoTo errorhandler
'disable screeen flickering
Application.ScreenUpdating = False
'set up range to search
Range("A1:A65000").Select

'sets the find option to match the userform-textbox to the range
Selection.Find(What:=Interiminput.regint.Value, LookIn:=xlValues,
SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate

'Message Box tells user what the name and reg is
MsgBox "Is this the correct Employer? " & ActiveCell.Offset(0, 2),
vbYesNo, " Confirm "
Dim Response As Variant
Response = MsgBox(Text, vbYesNo, Titletext)
If Response = vbYes Then
' submit the data to the relevant cells (working)
ActiveCell.Offset(0, 22).Range("A1").Select
ActiveCell.Value = (claimnoint)
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = (daysint)
' clear the cells for next entry
regint.Value = ""
claimnoint.Value = ""
daysint.Value = ""
MsgBox "Submitted", vbOKOnly, " Status "
Cells(1, 1).Select
Exit Sub
ElseIf Response = vbNo Then
Exit Sub
End If

'show message box if match not found
errorhandler:
MsgBox "Reg not found! Please retry", vbOKOnly, "Registration not
found"
regint.Value = ""
regint.SetFocus

End Sub

Thank you so much. Works perfectly. I must strive to understand the
properties of dim and why its needed as its something i have never
used!

Thanks again, your help is much appreciated.

Duncan