[RESOLVED] Can I use Forms?

Hi!

I've created a Macro to take data in Excel and convert it to a Word-formatted document.

When I run the macro, Word is launched in the background; and, you really can't do anyting to Excel until it is done. So, I want to add some sort of progress indicator - status bar or text status, it doesn't matter. (Status would be updated at every iteration or every 10 iterations.)

Do I use FORMS for this? Is there something better/different? And, can I update the status while the Macro is reading from Excel and writing to the Word doc?

I do have one Form, which prompts the user to input a filename:
Gen_Help_WordDoc_Form.Show
But, it seems like I have to click "Ok" or "Cancel" to get out and process everything else.

Melissa

Have you looked into the StatusBar property of the Excel Application object. I use this all the time when I'm running big pieces of code. It's the easiest way to give the user progress feedback.

Here's a sample piece of code for the StatusBar

VB Code:

Sub samplestatusupdate()
Dim MyStep As Integer
Dim AllSteps As Integer
Dim saveStatusBar As Boolean
 
saveStatusBar = Application.DisplayStatusBar
oldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
 
AllSteps = 10
For MyStep = 1 To AllSteps
    Application.StatusBar = "on Step " & CStr(MyStep) & " of " & CStr(AllSteps)
    MsgBox "Hi"
Next MyStep
 
Application.StatusBar = False
Application.DisplayStatusBar = saveStatusBar
 
End Sub

Perfect! That will work for me.


Thanks.