Close msgbox after making selection

[John Keith]

I make use of a msgbox with Yes/No buttons fairly frequently, like:

answer = msgbox("Do you want to proceed?", vbyesno)

After clicking on the desired button the message box window remains
open. Is there a way to force closing this window as soon as the
variable is read?


John Keith
(e-mail address removed)

 

[JLGWhiz]

Is this a message box that was created with a UserForm? The standard
message box automatically closes when a button is clicked. If it is the
standard message box and is not closing, there could be a software problem
in your system. Is this in Excel, if so, what version.

 

[John Keith]

Is this a message box that was created with a UserForm?

No

The standard message box automatically closes when a button is clicked. If it is the
standard message box and is not closing, there could be a software problem
in your system. Is this in Excel, if so, what version.

Excel 2007/WinXP

The message box window stays open until the next msgbox command is
executed.

I also just tried the following

msgbox("Test")
for i =1 to 100000000
a=1
next i
msgbox("done")

The loop has not completed yet and the first message box is still
open.





John Keith
(e-mail address removed)

 

[Peter T]

What you describe cannot and occur. I very much doubt you actually tested
the code you posted as an example, at least not exactly as written and with
nothing else.

My guess is you are showing the msgbox with screenupdating disabled. The
msgbox will stay on the screen indefinitely until the screen is refreshed
(even DoEvents) or by re-enabling screenupdating.

Be sure to temporarily disable screenupdating if/as necessary when showing a
msgbox.

Regards,
Peter T

The only thing that

 

[JLGWhiz]

I can't duplicate the problem in xl2003, but it sounds like a software
glitch to me. The standard message box is designed to close immediately
upon execution of a button. Unless xl2007 has an option to open the message
box as modeless, I cannot offer any explanation for the behavior.

 

[JLGWhiz]

Hi Peter, In the version of xl03 that I am running, the msgbox goes away,
whether or not ScreenUpdating is enabled. Is it different in xl07 + ?

 

[Peter T]

Try this

Sub abc()
' run with alt-F8
Dim a

'Application.ScreenUpdating = False

MsgBox ("Move me away from the centre of the screen")

Application.ScreenUpdating = False
For i = 1 To 100
a = 1
Next i
MsgBox ("done")
Application.ScreenUpdating = True
End Sub

Regards,
Peter T

 

[Peter T]

Forgot to mention I get same results in 2003 & 2007

Peter T

 

[JLGWhiz]

Nope, As soon as I click the button, the shape dialog box disappears. I
cannot get it to remain on screen after the button is clicked and cannot get
any further code to process without clicking the button. I believe that is
the designed performance.

 

[Peter T]

Indeed it is normal behaviour for the code to be suspended until the msgbox
is dismissed. But are you saying with screenupdating disabled the "image" of
the msgbox completely disappears from the screen after clicking OK.

Regards,
Peter T

 

[JLGWhiz]

Peter, That's what I'm saying. I tried the code you provided. I tried it
with and without Parentheses, with and without vbSystemModal and several
types of buttons and in default mode. It stops all activity until a button
is clicked and once the button is clicked, the dialog box disappears. I
cannot reproduce the effect you describe.

 

[John Keith]

What you describe cannot and occur. I very much doubt you actually tested
the code you posted as an example, at least not exactly as written and with
nothing else.

The maximum value of the for loop might not have been exactly what I
used but I did test the code and the message box was still there when
I went to bed and the loop was still merrily running.

I just retested again with the maximum value of the for loop set to 1
billion and the "test" message box remained on the screen for 30
seconds (1.7 GHz) until the "done" message appeared.

My guess is you are showing the msgbox with screenupdating disabled. The
msgbox will stay on the screen indefinitely until the screen is refreshed
(even DoEvents) or by re-enabling screenupdating.

I did not include a screenupdating statement (either true or false,
isn't the default true???)

Thank you for your comments.

Just as a further follow up I wanted to describe another aspect of the
message box remaining on the screen, and that is if I try to move it
I'll get multiple repaints of the box as I move it, it really fills
the screen rapidly.



John Keith
(e-mail address removed)

 

[Peter T]

Well how strange! Actually this issue crops up from time to time and AFAIK
the cause is slays as I suggested, namely a msgbox can appear to remain on
the screen after being dismissed if screen updating is disabled. I can
easily recreate that effect, in all versions.

Regards,
Peter T

 

[Peter T]

The maximum value of the for loop might not have been exactly what I
used but I did test the code and the message box was still there when
I went to bed and the loop was still merrily running.

I just retested again with the maximum value of the for loop set to 1
billion and the "test" message box remained on the screen for 30
seconds (1.7 GHz) until the "done" message appeared.

I did not include a screenupdating statement (either true or false,
isn't the default true???)

Thank you for your comments.

Just as a further follow up I wanted to describe another aspect of the
message box remaining on the screen, and that is if I try to move it
I'll get multiple repaints of the box as I move it, it really fills
the screen rapidly.

That perfectly describes the effect of showing a msgbox with screenupdating
disabled. Include this line both just before and after showing the msgbox
(the one that stays on the screen and repaints if you move it)

Debug.Print Application.Screenupdating

Open the Immediate window to view the result, ctrl-g

Also try the code sample I included in a response to JLGWhiz, and uncomment
the first screenupdating line.

Regards,
Peter T