<p>I have created a database report generator in Excel. I am trying to create a dialog box that displays status information as the program runs.</p>

<p>When I generate the report, although the dialog box appears, I cannot refresh/update the information it displays. Most of the time, the dialog box only partially appears. I have tried using the .repaint method, but I still get the same results. I only see the complete dialog box, after the report is generated.</p>

[{'answer_id': 51946, 'author': 'Gulzar Nazim', 'author_id': 4337, 'author_profile': 'https://Stackoverflow.com/users/4337', 'pm_score': 0, 'selected': False, 'text': '<p>The dialog box is also running on the same UI thread. So, it is too busy to repaint itself. Not sure if VBA has good multi-threading capabilities.</p>\n'}, {'answer_id': 51953, 'author': 'Galwegian', 'author_id': 3201, 'author_profile': 'https://Stackoverflow.com/users/3201', 'pm_score': 3, 'selected': False, 'text': '<p>I have used Excel\'s own status bar (in bottom left of window) to display progress information for a similar application I developed in the past.</p>\n\n<p>It works out very well if you just want to display textual updates on progress, and avoids the need for an updating dialog at all.</p>\n\n<p>Ok @JonnyGold, here\'s an example of the kind of thing I used...</p>\n\n<pre><code>Sub StatusBarExample()\n    Application.ScreenUpdating = False \n    \' turns off screen updating\n    Application.DisplayStatusBar = True \n    \' makes sure that the statusbar is visible\n    Application.StatusBar = "Please wait while performing task 1..."\n    \' add some code for task 1 that replaces the next sentence\n    Application.Wait Now + TimeValue("00:00:02")\n    Application.StatusBar = "Please wait while performing task 2..."\n    \' add some code for task 2 that replaces the next sentence\n    Application.Wait Now + TimeValue("00:00:02")\n    Application.StatusBar = False \n    \' gives control of the statusbar back to the programme\nEnd Sub\n</code></pre>\n\n<p>Hope this helps!</p>\n'}, {'answer_id': 51957, 'author': 'Mark Biek', 'author_id': 305, 'author_profile': 'https://Stackoverflow.com/users/305', 'pm_score': 3, 'selected': False, 'text': '<p>Try adding a <strong>DoEvents</strong> call in your loop.  That should allow the form to repaint &amp; accept other requests.</p>\n'}, {'answer_id': 91978, 'author': 'Robert Mearns', 'author_id': 5050, 'author_profile': 'https://Stackoverflow.com/users/5050', 'pm_score': 3, 'selected': True, 'text': '<p>The code below works well when performing actions within Excel (XP or later).</p>\n\n<p>For actions that take place outside Excel, for example connecting to a database and retrieving data the best this offers is the opportunity to show dialogs before and after the action (e.g. <em>"Getting data"</em>, <em>"Got data"</em>)</p>\n\n<p>Create a form called <strong>"frmStatus"</strong>, put a label on the form called <strong>"Label1"</strong>.</p>\n\n<p>Set the form property <strong>\'ShowModal\' = false</strong>, this allows the code to run while the form is displayed.</p>\n\n<pre><code>Sub ShowForm_DoSomething()\n\n    Load frmStatus\n    frmStatus.Label1.Caption = "Starting"\n    frmStatus.Show\n    frmStatus.Repaint\n\'Load the form and set text\n\n    frmStatus.Label1.Caption = "Doing something"\n    frmStatus.Repaint\n\n\'code here to perform an action\n\n    frmStatus.Label1.Caption = "Doing something else"\n    frmStatus.Repaint\n\n\'code here to perform an action\n\n    frmStatus.Label1.Caption = "Finished"\n    frmStatus.Repaint\n    Application.Wait (Now + TimeValue("0:00:01"))\n    frmStatus.Hide\n    Unload frmStatus\n\'hide and unload the form\n\nEnd Sub\n</code></pre>\n'}, {'answer_id': 17247251, 'author': 'Richard Knott', 'author_id': 2510909, 'author_profile': 'https://Stackoverflow.com/users/2510909', 'pm_score': 2, 'selected': False, 'text': '<p>Insert a blank sheet in your workbook\nRename the Sheet  eg. "information"</p>\n\n<pre><code>Sheets("information").Select\nRange("C3").Select\nActiveCell.FormulaR1C1 = "Updating Records"\nApplication.ScreenUpdating = False\nApplication.Wait Now + TimeValue("00:00:02")\n</code></pre>\n\n<p><em>Continue Macro</em></p>\n\n<pre><code>Sheets("information").Select\nRange("C3").Select\nApplication.ScreenUpdating = True\nActiveCell.FormulaR1C1 = "Preparing Information"\nApplication.ScreenUpdating = False\nApplication.Wait Now + TimeValue("00:00:02")\n</code></pre>\n\n<p><em>Continue Macro</em></p>\n\n<p>Etc\nAlternatively select a blank cell somewhere on the existing sheet instead of inserting a new sheet</p>\n\n<pre><code>Range("C3").Select\nActiveCell.FormulaR1C1 = "Updating Records"\nApplication.ScreenUpdating = False\nApplication.Wait Now + TimeValue("00:00:02")\n</code></pre>\n\n<p>Etc</p>\n'}]
['https://Stackoverflow.com/questions/51941', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2665/']