Answering Message boxes
 

Hello,

I have a question on how to answer message boxes programmatically.
For example: Answering [No] when closing a file and the message box prompts
me to save.
I've programmed in Lotus before and I used this command:
AnswerMsgBox (Value)

The command had to be entered on the line preceding the message box
AnswerMsgBox No
Application.Quit
In this example the program does not prompt to save before closing.

Is there a similar command in Excel?

Thanx!

Answering Message boxes
 

Hello,

You might want to consider this approach:

Dim myAnswer as String

myAnswer = MsgBox "Do you want to Save", vbYesNo

if myAnswer = vbYes then
.... 'do something
else
... ' do something else ...
end if

There are several options you can do with MsgBox.

Hope this helps,
Chad


"isbjornen" wrote:

Hello,

I have a question on how to answer message boxes programmatically.
For example: Answering [No] when closing a file and the message box prompts
me to save.
I've programmed in Lotus before and I used this command:
AnswerMsgBox (Value)

The command had to be entered on the line preceding the message box
AnswerMsgBox No
Application.Quit
In this example the program does not prompt to save before closing.

Is there a similar command in Excel?

Thanx!

Answering Message boxes
 

Dim myAnswer as String

This is incorrect. You should use

Dim myAnswer as Long
or, better (in Excel 2000 and later),
Dim myAnswer As VbMsgBoxResult


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)



"ChadF" wrote in message
...

Hello,

You might want to consider this approach:

Dim myAnswer as String

myAnswer = MsgBox "Do you want to Save", vbYesNo

if myAnswer = vbYes then
.... 'do something
else
... ' do something else ...
end if

There are several options you can do with MsgBox.

Hope this helps,
Chad


"isbjornen" wrote:

Hello,

I have a question on how to answer message boxes programmatically.
For example: Answering [No] when closing a file and the message box
prompts
me to save.
I've programmed in Lotus before and I used this command:
AnswerMsgBox (Value)

The command had to be entered on the line preceding the message box
AnswerMsgBox No
Application.Quit
In this example the program does not prompt to save before closing.

Is there a similar command in Excel?

Thanx!

Answering Message boxes
 

or, to avoid "dim"ing anything at all, you can use an if-then
construct.

If Msgbox ("Do you want to quit?",vbYesNo + vbInformation) = vbNo Then
Exit Sub 'or close file, or whatever
Else
(whatever)
End If

susan
(who must be moving up in the excel
world if she's adding info to mvp's!!!)
ha ha

Answering Message boxes
 

Indeed. Thanks Chip.


"Chip Pearson" wrote:

Dim myAnswer as String

This is incorrect. You should use

Dim myAnswer as Long
or, better (in Excel 2000 and later),
Dim myAnswer As VbMsgBoxResult


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)



"ChadF" wrote in message
...

Hello,

You might want to consider this approach:

Dim myAnswer as String

myAnswer = MsgBox "Do you want to Save", vbYesNo

if myAnswer = vbYes then
.... 'do something
else
... ' do something else ...
end if

There are several options you can do with MsgBox.

Hope this helps,
Chad


"isbjornen" wrote:

Hello,

I have a question on how to answer message boxes programmatically.
For example: Answering [No] when closing a file and the message box
prompts
me to save.
I've programmed in Lotus before and I used this command:
AnswerMsgBox (Value)

The command had to be entered on the line preceding the message box
AnswerMsgBox No
Application.Quit
In this example the program does not prompt to save before closing.

Is there a similar command in Excel?

Thanx!

Answering Message boxes
 

Thanks, but I'm trying to avoid having a default Windows messagebox popping
up and pausing the macro. In the example below, I don't want to save before
exiting the program, i.e. I want the answer to be [No] without clicking.

"ChadF" wrote:


Hello,

You might want to consider this approach:

Dim myAnswer as String

myAnswer = MsgBox "Do you want to Save", vbYesNo

if myAnswer = vbYes then
.... 'do something
else
... ' do something else ...
end if

There are several options you can do with MsgBox.

Hope this helps,
Chad


"isbjornen" wrote:

Hello,

I have a question on how to answer message boxes programmatically.
For example: Answering [No] when closing a file and the message box prompts
me to save.
I've programmed in Lotus before and I used this command:
AnswerMsgBox (Value)

The command had to be entered on the line preceding the message box
AnswerMsgBox No
Application.Quit
In this example the program does not prompt to save before closing.

Is there a similar command in Excel?

Thanx!

Answering Message boxes
 

then why have a message box at all?
the purpose of the message box is to either inform the
user of something, or get their input as to a simple
yes/no/cancel-type decision.
if you want the code to do something automatically,
then program it in the code.

or am i missing the point entirely?
susan

Answering Message boxes
 

What you really want to do is to avoid the message box entirely. You can do
this by adding the following to the ThisWorkbook module (Right click the XL
icon next to the word file and select view code). Past this in the code
window...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub

XL amintains a flag which lets it know if a save is required. by executing
this in the before close event we are setting that flag to saved so that XL
now beleives that there is nothing to save and it will exet without the
prompt...
--
HTH...

Jim Thomlinson


"isbjornen" wrote:

Thanks, but I'm trying to avoid having a default Windows messagebox popping
up and pausing the macro. In the example below, I don't want to save before
exiting the program, i.e. I want the answer to be [No] without clicking.

"ChadF" wrote:


Hello,

You might want to consider this approach:

Dim myAnswer as String

myAnswer = MsgBox "Do you want to Save", vbYesNo

if myAnswer = vbYes then
.... 'do something
else
... ' do something else ...
end if

There are several options you can do with MsgBox.

Hope this helps,
Chad


"isbjornen" wrote:

Hello,

I have a question on how to answer message boxes programmatically.
For example: Answering [No] when closing a file and the message box prompts
me to save.
I've programmed in Lotus before and I used this command:
AnswerMsgBox (Value)

The command had to be entered on the line preceding the message box
AnswerMsgBox No
Application.Quit
In this example the program does not prompt to save before closing.

Is there a similar command in Excel?

Thanx!

Answering Message boxes
 

Thanks, this answer half of my question - specific for not saving a workbook.
However, what I'm looking for is a generic command/statement that can take
care of every situation of a default Windows Message. Sometimes I might want
to answer [OK], or [Retry] etc. depending on the situation.

Is there a generic command/statement?


"Jim Thomlinson" wrote:

What you really want to do is to avoid the message box entirely. You can do
this by adding the following to the ThisWorkbook module (Right click the XL
icon next to the word file and select view code). Past this in the code
window...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub

XL amintains a flag which lets it know if a save is required. by executing
this in the before close event we are setting that flag to saved so that XL
now beleives that there is nothing to save and it will exet without the
prompt...
--
HTH...

Jim Thomlinson


"isbjornen" wrote:

Thanks, but I'm trying to avoid having a default Windows messagebox popping
up and pausing the macro. In the example below, I don't want to save before
exiting the program, i.e. I want the answer to be [No] without clicking.

"ChadF" wrote:


Hello,

You might want to consider this approach:

Dim myAnswer as String

myAnswer = MsgBox "Do you want to Save", vbYesNo

if myAnswer = vbYes then
.... 'do something
else
... ' do something else ...
end if

There are several options you can do with MsgBox.

Hope this helps,
Chad


"isbjornen" wrote:

Hello,

I have a question on how to answer message boxes programmatically.
For example: Answering [No] when closing a file and the message box prompts
me to save.
I've programmed in Lotus before and I used this command:
AnswerMsgBox (Value)

The command had to be entered on the line preceding the message box
AnswerMsgBox No
Application.Quit
In this example the program does not prompt to save before closing.

Is there a similar command in Excel?

Thanx!

Answering Message boxes
 

Reading carefully what isbjornen wrote, I believe the line he wants to use is:
ThisWorkbook.Saved = True

This prevents the Excel software from automatically asking if you want to
save. To incorporate it into your code in response to a MsgBox you would
most likely have to use an If statement like:
If MsgBoxResponse = vbNo Then
ThisWorkbook.Saved = True
End If

Maybe we are confusing Message Box and Dialog Box?

"isbjornen" wrote:

Thanks, but I'm trying to avoid having a default Windows messagebox popping
up and pausing the macro. In the example below, I don't want to save before
exiting the program, i.e. I want the answer to be [No] without clicking.

"ChadF" wrote:


Hello,

You might want to consider this approach:

Dim myAnswer as String

myAnswer = MsgBox "Do you want to Save", vbYesNo

if myAnswer = vbYes then
.... 'do something
else
... ' do something else ...
end if

There are several options you can do with MsgBox.

Hope this helps,
Chad


"isbjornen" wrote:

Hello,

I have a question on how to answer message boxes programmatically.
For example: Answering [No] when closing a file and the message box prompts
me to save.
I've programmed in Lotus before and I used this command:
AnswerMsgBox (Value)

The command had to be entered on the line preceding the message box
AnswerMsgBox No
Application.Quit
In this example the program does not prompt to save before closing.

Is there a similar command in Excel?

Thanx!

Answering Message boxes
 

I'm talking about default Windows messages - try this code in a new workbook
(don't save it):

Sub test()
Application.Quit
End Sub

When you run the code Windows automatically prompts you to save and pauses
your code until you close the message box.

Sometimes it is possible to use SendKeys; however, I don't think it's very
reliable.

"Susan" wrote:

then why have a message box at all?
the purpose of the message box is to either inform the
user of something, or get their input as to a simple
yes/no/cancel-type decision.
if you want the code to do something automatically,
then program it in the code.

or am i missing the point entirely?
susan

Answering Message boxes
 

Yes, of course I meant Dialog box - sorry!!! [blushing]

"JLGWhiz" wrote:

Reading carefully what isbjornen wrote, I believe the line he wants to use is:
ThisWorkbook.Saved = True

This prevents the Excel software from automatically asking if you want to
save. To incorporate it into your code in response to a MsgBox you would
most likely have to use an If statement like:
If MsgBoxResponse = vbNo Then
ThisWorkbook.Saved = True
End If

Maybe we are confusing Message Box and Dialog Box?

"isbjornen" wrote:

Thanks, but I'm trying to avoid having a default Windows messagebox popping
up and pausing the macro. In the example below, I don't want to save before
exiting the program, i.e. I want the answer to be [No] without clicking.

"ChadF" wrote:


Hello,

You might want to consider this approach:

Dim myAnswer as String

myAnswer = MsgBox "Do you want to Save", vbYesNo

if myAnswer = vbYes then
.... 'do something
else
... ' do something else ...
end if

There are several options you can do with MsgBox.

Hope this helps,
Chad


"isbjornen" wrote:

Hello,

I have a question on how to answer message boxes programmatically.
For example: Answering [No] when closing a file and the message box prompts
me to save.
I've programmed in Lotus before and I used this command:
AnswerMsgBox (Value)

The command had to be entered on the line preceding the message box
AnswerMsgBox No
Application.Quit
In this example the program does not prompt to save before closing.

Is there a similar command in Excel?

Thanx!

Answering Message boxes
 

By coding correctly you can avoid having the prompts come up (with the
exception of security promts which are non-defeatable for a reason). Most of
the promts can be avoided using

Application.DisplayAlerts = false
'Your Code
Application.DisplayAlerts = true

To your question specifically... No there is no way to answer a message box.
But as I have ointed out there reeally is no need to. You can use some VB
Script to answer message boxes (internal or External) but that is a bit of
work and not really recommended for the newly initiated or the faint of heart.
--
HTH...

Jim Thomlinson


"isbjornen" wrote:

Thanks, this answer half of my question - specific for not saving a workbook.
However, what I'm looking for is a generic command/statement that can take
care of every situation of a default Windows Message. Sometimes I might want
to answer [OK], or [Retry] etc. depending on the situation.

Is there a generic command/statement?


"Jim Thomlinson" wrote:

What you really want to do is to avoid the message box entirely. You can do
this by adding the following to the ThisWorkbook module (Right click the XL
icon next to the word file and select view code). Past this in the code
window...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub

XL amintains a flag which lets it know if a save is required. by executing
this in the before close event we are setting that flag to saved so that XL
now beleives that there is nothing to save and it will exet without the
prompt...
--
HTH...

Jim Thomlinson


"isbjornen" wrote:

Thanks, but I'm trying to avoid having a default Windows messagebox popping
up and pausing the macro. In the example below, I don't want to save before
exiting the program, i.e. I want the answer to be [No] without clicking.

"ChadF" wrote:


Hello,

You might want to consider this approach:

Dim myAnswer as String

myAnswer = MsgBox "Do you want to Save", vbYesNo

if myAnswer = vbYes then
.... 'do something
else
... ' do something else ...
end if

There are several options you can do with MsgBox.

Hope this helps,
Chad


"isbjornen" wrote:

Hello,

I have a question on how to answer message boxes programmatically.
For example: Answering [No] when closing a file and the message box prompts
me to save.
I've programmed in Lotus before and I used this command:
AnswerMsgBox (Value)

The command had to be entered on the line preceding the message box
AnswerMsgBox No
Application.Quit
In this example the program does not prompt to save before closing.

Is there a similar command in Excel?

Thanx!

Answering Message boxes
 

Thanks!

"Jim Thomlinson" wrote:

By coding correctly you can avoid having the prompts come up (with the
exception of security promts which are non-defeatable for a reason). Most of
the promts can be avoided using

Application.DisplayAlerts = false
'Your Code
Application.DisplayAlerts = true

To your question specifically... No there is no way to answer a message box.
But as I have ointed out there reeally is no need to. You can use some VB
Script to answer message boxes (internal or External) but that is a bit of
work and not really recommended for the newly initiated or the faint of heart.
--
HTH...

Jim Thomlinson


"isbjornen" wrote:

Thanks, this answer half of my question - specific for not saving a workbook.
However, what I'm looking for is a generic command/statement that can take
care of every situation of a default Windows Message. Sometimes I might want
to answer [OK], or [Retry] etc. depending on the situation.

Is there a generic command/statement?


"Jim Thomlinson" wrote:

What you really want to do is to avoid the message box entirely. You can do
this by adding the following to the ThisWorkbook module (Right click the XL
icon next to the word file and select view code). Past this in the code
window...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub

XL amintains a flag which lets it know if a save is required. by executing
this in the before close event we are setting that flag to saved so that XL
now beleives that there is nothing to save and it will exet without the
prompt...
--
HTH...

Jim Thomlinson


"isbjornen" wrote:

Thanks, but I'm trying to avoid having a default Windows messagebox popping
up and pausing the macro. In the example below, I don't want to save before
exiting the program, i.e. I want the answer to be [No] without clicking.

"ChadF" wrote:


Hello,

You might want to consider this approach:

Dim myAnswer as String

myAnswer = MsgBox "Do you want to Save", vbYesNo

if myAnswer = vbYes then
.... 'do something
else
... ' do something else ...
end if

There are several options you can do with MsgBox.

Hope this helps,
Chad


"isbjornen" wrote:

Hello,

I have a question on how to answer message boxes programmatically.
For example: Answering [No] when closing a file and the message box prompts
me to save.
I've programmed in Lotus before and I used this command:
AnswerMsgBox (Value)

The command had to be entered on the line preceding the message box
AnswerMsgBox No
Application.Quit
In this example the program does not prompt to save before closing.

Is there a similar command in Excel?

Thanx!