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!

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!

> 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" <(E-Mail Removed)> wrote in message
news:B9B0A7A5-A19B-4812-B4DC-(E-Mail Removed)...
>
> 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!

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

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" <(E-Mail Removed)> wrote in message
> news:B9B0A7A5-A19B-4812-B4DC-(E-Mail Removed)...
> >
> > 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!
>
>
>

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!

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

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!

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!

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!