Basically I have a macro which contains a vbYesNoCancel message box. I need
the macro to run a sub-routine when the user clicks 'cancel'. At the moment
it just escapes out of the macro and stops. I don't seem to be able to
manipulate the vbCancel as an elseif for the user response. Is there any kind
of onCancel functionality or another way to run a sub-routine when the user
clicks cancel? Or do I have to create my own dialog box from scratch?

Sub tester1()
Dim ans As Long
ans = MsgBox("What to do", vbYesNoCancel)
Select Case ans
Case vbYes
MsgBox "You said Yes"
Case vbNo
MsgBox "You said No"
Case vbCancel
MsgBox "You said cancel"
End Select

End Sub

Works for me.

--
Regards,
Tom Ogilvy




"Dave" wrote in message
...
Basically I have a macro which contains a vbYesNoCancel message box. I
need
the macro to run a sub-routine when the user clicks 'cancel'. At the
moment
it just escapes out of the macro and stops. I don't seem to be able to
manipulate the vbCancel as an elseif for the user response. Is there any
kind
of onCancel functionality or another way to run a sub-routine when the
user
clicks cancel? Or do I have to create my own dialog box from scratch?

Thanks, I was trying if and else ifs - this seems like it should solve the
problem, much appreciated!!!!!!!!!!!!

"Tom Ogilvy" wrote:

Sub tester1()
Dim ans As Long
ans = MsgBox("What to do", vbYesNoCancel)
Select Case ans
Case vbYes
MsgBox "You said Yes"
Case vbNo
MsgBox "You said No"
Case vbCancel
MsgBox "You said cancel"
End Select

End Sub

Works for me.

--
Regards,
Tom Ogilvy




"Dave" wrote in message
...
Basically I have a macro which contains a vbYesNoCancel message box. I
need
the macro to run a sub-routine when the user clicks 'cancel'. At the
moment
it just escapes out of the macro and stops. I don't seem to be able to
manipulate the vbCancel as an elseif for the user response. Is there any
kind
of onCancel functionality or another way to run a sub-routine when the
user
clicks cancel? Or do I have to create my own dialog box from scratch?

Enclose the Right part after msgbox in to paranthesis and assign the
response of msgbox to a variable

e.g

Dim userResponse As String

userResponse = MsgBox ("Do you want to save changes?"
If userResponse = vbCancel Then
'code if cancel is clicked
End If

OR if you are working on all 3 possible responses then

Select Case userResponse
Case vbOK
'code if OK clicked
Case vbNO
'code if NO clicked
Case vbCancel
'code if Cancel clicked
End Select

Sharad

"Dave" wrote in message
...
Basically I have a macro which contains a vbYesNoCancel message box. I
need
the macro to run a sub-routine when the user clicks 'cancel'. At the
moment
it just escapes out of the macro and stops. I don't seem to be able to
manipulate the vbCancel as an elseif for the user response. Is there any
kind
of onCancel functionality or another way to run a sub-routine when the
user
clicks cancel? Or do I have to create my own dialog box from scratch?

corrections:

1. Dim userResponse As Integer
"Though String will work, this is correct type.

2. MsgBox ("Do you want to save changes?", vbYesNoCancel)

But I realize that by the time I write this corrections, you already
got other better responses from Tom and an AlphaNumeric fellow ;-)

cheers

Sharad

"Sharad Naik" wrote in message
...
Enclose the Right part after msgbox in to paranthesis and assign the
response of msgbox to a variable

e.g

Dim userResponse As String

userResponse = MsgBox ("Do you want to save changes?"
If userResponse = vbCancel Then
'code if cancel is clicked
End If

OR if you are working on all 3 possible responses then

Select Case userResponse
Case vbOK
'code if OK clicked
Case vbNO
'code if NO clicked
Case vbCancel
'code if Cancel clicked
End Select

Sharad

"Dave" wrote in message
...
Basically I have a macro which contains a vbYesNoCancel message box. I
need
the macro to run a sub-routine when the user clicks 'cancel'. At the
moment
it just escapes out of the macro and stops. I don't seem to be able to
manipulate the vbCancel as an elseif for the user response. Is there any
kind
of onCancel functionality or another way to run a sub-routine when the
user
clicks cancel? Or do I have to create my own dialog box from scratch?

1. Dim userResponse As Integer

To be totally accurate, you should declare the variable as a
Long.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Sharad Naik" wrote in message
...
corrections:

1. Dim userResponse As Integer
"Though String will work, this is correct type.

2. MsgBox ("Do you want to save changes?", vbYesNoCancel)

But I realize that by the time I write this corrections, you
already
got other better responses from Tom and an AlphaNumeric fellow
;-)

cheers

Sharad

"Sharad Naik" wrote in message
...
Enclose the Right part after msgbox in to paranthesis and
assign the response of msgbox to a variable

e.g

Dim userResponse As String

userResponse = MsgBox ("Do you want to save changes?"
If userResponse = vbCancel Then
'code if cancel is clicked
End If

OR if you are working on all 3 possible responses then

Select Case userResponse
Case vbOK
'code if OK clicked
Case vbNO
'code if NO clicked
Case vbCancel
'code if Cancel clicked
End Select

Sharad

"Dave" wrote in message
...
Basically I have a macro which contains a vbYesNoCancel
message box. I need
the macro to run a sub-routine when the user clicks 'cancel'.
At the moment
it just escapes out of the macro and stops. I don't seem to
be able to
manipulate the vbCancel as an elseif for the user response.
Is there any kind
of onCancel functionality or another way to run a sub-routine
when the user
clicks cancel? Or do I have to create my own dialog box from
scratch?

This is what the Help on Message box tells:

"Displays a message in a dialog box, waits for the user to click a button,
and returns an Integer indicating which button the user clicked."

Sharad

"Chip Pearson" wrote in message
...
1. Dim userResponse As Integer

To be totally accurate, you should declare the variable as a Long.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Sharad Naik" wrote in message
...
corrections:

1. Dim userResponse As Integer
"Though String will work, this is correct type.

2. MsgBox ("Do you want to save changes?", vbYesNoCancel)

But I realize that by the time I write this corrections, you already
got other better responses from Tom and an AlphaNumeric fellow ;-)

cheers

Sharad

"Sharad Naik" wrote in message
...
Enclose the Right part after msgbox in to paranthesis and assign the
response of msgbox to a variable

e.g

Dim userResponse As String

userResponse = MsgBox ("Do you want to save changes?"
If userResponse = vbCancel Then
'code if cancel is clicked
End If

OR if you are working on all 3 possible responses then

Select Case userResponse
Case vbOK
'code if OK clicked
Case vbNO
'code if NO clicked
Case vbCancel
'code if Cancel clicked
End Select

Sharad

"Dave" wrote in message
...
Basically I have a macro which contains a vbYesNoCancel message box. I
need
the macro to run a sub-routine when the user clicks 'cancel'. At the
moment
it just escapes out of the macro and stops. I don't seem to be able to
manipulate the vbCancel as an elseif for the user response. Is there
any kind
of onCancel functionality or another way to run a sub-routine when the
user
clicks cancel? Or do I have to create my own dialog box from scratch?

"Help" has confused me in various places like this. I wonder if in this case
"integer" mearly means a whole number.

If I try
dim x
x = 12: x = "abc"
x = MsgBox(123, vbYesNoCancel)
and after clicking the msgbox I look in the Locals window, x is indicated as
Variant/Long

Regards,
Peter T


"Sharad Naik" wrote in message
...
This is what the Help on Message box tells:

"Displays a message in a dialog box, waits for the user to click a button,
and returns an Integer indicating which button the user clicked."

Sharad

"Chip Pearson" wrote in message
...
1. Dim userResponse As Integer

To be totally accurate, you should declare the variable as a Long.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Sharad Naik" wrote in message
...
corrections:

1. Dim userResponse As Integer
"Though String will work, this is correct type.

2. MsgBox ("Do you want to save changes?", vbYesNoCancel)

But I realize that by the time I write this corrections, you already
got other better responses from Tom and an AlphaNumeric fellow ;-)

cheers

Sharad

"Sharad Naik" wrote in message
...
Enclose the Right part after msgbox in to paranthesis and assign the
response of msgbox to a variable

e.g

Dim userResponse As String

userResponse = MsgBox ("Do you want to save changes?"
If userResponse = vbCancel Then
'code if cancel is clicked
End If

OR if you are working on all 3 possible responses then

Select Case userResponse
Case vbOK
'code if OK clicked
Case vbNO
'code if NO clicked
Case vbCancel
'code if Cancel clicked
End Select

Sharad

"Dave" wrote in message
...
Basically I have a macro which contains a vbYesNoCancel message box.
I
need
the macro to run a sub-routine when the user clicks 'cancel'. At the
moment
it just escapes out of the macro and stops. I don't seem to be able
to
manipulate the vbCancel as an elseif for the user response. Is there
any kind
of onCancel functionality or another way to run a sub-routine when
the
user
clicks cancel? Or do I have to create my own dialog box from scratch?

From the immediate window:

? typename(msgbox( vbYesNo))
Long

and the constants are typed as Long

Must be they haven't updated the help from the 16 bit version.

--
Regards,
Tom Ogilvy

"Sharad Naik" wrote in message
...
This is what the Help on Message box tells:

"Displays a message in a dialog box, waits for the user to click a button,
and returns an Integer indicating which button the user clicked."

Sharad

"Chip Pearson" wrote in message
...
1. Dim userResponse As Integer

To be totally accurate, you should declare the variable as a Long.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Sharad Naik" wrote in message
...
corrections:

1. Dim userResponse As Integer
"Though String will work, this is correct type.

2. MsgBox ("Do you want to save changes?", vbYesNoCancel)

But I realize that by the time I write this corrections, you already
got other better responses from Tom and an AlphaNumeric fellow ;-)

cheers

Sharad

"Sharad Naik" wrote in message
...
Enclose the Right part after msgbox in to paranthesis and assign the
response of msgbox to a variable

e.g

Dim userResponse As String

userResponse = MsgBox ("Do you want to save changes?"
If userResponse = vbCancel Then
'code if cancel is clicked
End If

OR if you are working on all 3 possible responses then

Select Case userResponse
Case vbOK
'code if OK clicked
Case vbNO
'code if NO clicked
Case vbCancel
'code if Cancel clicked
End Select

Sharad

"Dave" wrote in message
...
Basically I have a macro which contains a vbYesNoCancel message box.
I
need
the macro to run a sub-routine when the user clicks 'cancel'. At the
moment
it just escapes out of the macro and stops. I don't seem to be able
to
manipulate the vbCancel as an elseif for the user response. Is there
any kind
of onCancel functionality or another way to run a sub-routine when
the
user
clicks cancel? Or do I have to create my own dialog box from scratch?

Thanks Chip, forget my just previous post.
Sharad
"Sharad Naik" wrote in message
...
This is what the Help on Message box tells:

"Displays a message in a dialog box, waits for the user to click a button,
and returns an Integer indicating which button the user clicked."

Sharad

"Chip Pearson" wrote in message
...
1. Dim userResponse As Integer

To be totally accurate, you should declare the variable as a Long.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Sharad Naik" wrote in message
...
corrections:

1. Dim userResponse As Integer
"Though String will work, this is correct type.

2. MsgBox ("Do you want to save changes?", vbYesNoCancel)

But I realize that by the time I write this corrections, you already
got other better responses from Tom and an AlphaNumeric fellow ;-)

cheers

Sharad

"Sharad Naik" wrote in message
...
Enclose the Right part after msgbox in to paranthesis and assign the
response of msgbox to a variable

e.g

Dim userResponse As String

userResponse = MsgBox ("Do you want to save changes?"
If userResponse = vbCancel Then
'code if cancel is clicked
End If

OR if you are working on all 3 possible responses then

Select Case userResponse
Case vbOK
'code if OK clicked
Case vbNO
'code if NO clicked
Case vbCancel
'code if Cancel clicked
End Select

Sharad

"Dave" wrote in message
...
Basically I have a macro which contains a vbYesNoCancel message box. I
need
the macro to run a sub-routine when the user clicks 'cancel'. At the
moment
it just escapes out of the macro and stops. I don't seem to be able to
manipulate the vbCancel as an elseif for the user response. Is there
any kind
of onCancel functionality or another way to run a sub-routine when the
user
clicks cancel? Or do I have to create my own dialog box from scratch?

This works for me; try to adapt it.

Sub xx()
Select Case MsgBox("MSG", vbCritical + vbYesNoCancel)
Case vbYes
MsgBox "yes"
Case vbNo
MsgBox "no"
Case vbCancel
MsgBox "cancel"
End Select
End Sub


"Dave" wrote:

Basically I have a macro which contains a vbYesNoCancel message box. I need
the macro to run a sub-routine when the user clicks 'cancel'. At the moment
it just escapes out of the macro and stops. I don't seem to be able to
manipulate the vbCancel as an elseif for the user response. Is there any kind
of onCancel functionality or another way to run a sub-routine when the user
clicks cancel? Or do I have to create my own dialog box from scratch?