Close msgbox when ScreenUpdating is disabled

**jhache** · 09-12-2012, 11:56 AM

I have a fairly long procedure to update data in a table using other workbooks on a server. During that procedure, there are several logical tests that happen to make sure everything makes sense. When something seems illogical, a msgbox pops up to ask the user to chose whether to modify the data or keep the current entry. During the whole code, the ScreenUpdating property is set to false in order to hide all the data manipulation and workbooks opening and closing, but my problem is the msgboxes simply pile up on top of each other, not to mention the fact that it is difficult for the user to distinguish between a box for which he clicked on "yes" or "no", or a new box that just poped up over it.

Basically my question is: Is there a way to force a message box to close or repaint only the message box instead of turning ScreenUpdating to true and right back to false after displaying the msgbox (I think this method is a bit messy and probably not good practice).

Thank you.

**JosephP** · 09-12-2012, 12:02 PM

you may be able to redraw the specific rectangle using api calls but it would be easier in my opinion to use a modeless form that you display while the processing occurs and put any required messages onto that (you can use the repaint method of the form as required).

**jhache** · 09-12-2012, 02:25 PM

Thanks for your input, it's a great idea to create a userform instead of using a msgbox. Since all I want my message box to return is a simple yes or no answer, I made my previously VbMsgBoxResult variable a public string and put this as my userform yes/no buttons:

Please Login or Register  to view this content.

Unfortunately all this didn't fix much since I need my code to wait for an answer before it keeps running so I can't use my form as modeless. Plus, it didn't do anything about making the form disappear after the user clicks yes or no. The "Me.hide" doesn't seem to have an immediate effect because of the ScreenUpdating property. Is there anything I can put after/instead of "Me.hide" (I've tried Unload me too but I got the same result) that would make the form disappear? Any ideas? Thanks.

**JosephP** · 09-12-2012, 05:03 PM

you can put a multipage control on the form and use one page as your message box. the calling code would populate a label control with the message and then activate the correct page. it then needs to loop using doevents until a button has been pressed on the form which sets a form variable-so you know what the response was-and reactivates the progress bar tab. (the doevents loop waits until the multipage control's value is 0)
of course you may find it simpler to refresh the screen or hide the application instead