how do I make a routine run after the 'cancel' butten is pressed .
 

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?

how do I make a routine run after the 'cancel' butten is pressed .
 

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?

how do I make a routine run after the 'cancel' butten is pressed .
 

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?

how do I make a routine run after the 'cancel' butten is pressed .
 

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?

how do I make a routine run after the 'cancel' butten is pressed .
 

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?

how do I make a routine run after the 'cancel' butten is press
 

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?

how do I make a routine run after the 'cancel' butten is pressed .
 

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?

how do I make a routine run after the 'cancel' butten is pressed .
 

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?

how do I make a routine run after the 'cancel' butten is pressed .
 

"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?

how do I make a routine run after the 'cancel' butten is pressed .
 

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?

how do I make a routine run after the 'cancel' butten is pressed .
 

Thanks Tom, and Peter
So it's a "Long" way to learn from 'Help'

Sharad


"Tom Ogilvy" wrote in message
...
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?

how do I make a routine run after the 'cancel' butten is pressed .
 

Hi Peter,

"integer" mearly means a whole number

A happy coalescence of merely and nearly? <vbg

---
Regards,
Norman



"Peter T" <peter_t@discussions wrote in message
...
"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

how do I make a routine run after the 'cancel' butten is pressed .
 

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?

how do I make a routine run after the 'cancel' butten is pressed .
 

:-)
and - scope here for a new vb function

Coalesce(x, y) 'join together to form a "whole"

Regards,
Peter

"Norman Jones" wrote in message
...
Hi Peter,

"integer" mearly means a whole number

A happy coalescence of merely and nearly? <vbg

---
Regards,
Norman