Have a windoe pop up while macro is running

[richiwatts]

At present my MS Word Macro uses the following for the Statusbar while the Macro is running.

For indx = 1 To UBound(WordArray)
If InStr(WholeDocument, LCase$(WordArray(indx))) > 0 Then
.Text = WordArray(indx)
.Execute Replace:=wdReplaceAll
End If
StatusBar = "Scanning document, please wait =" & UBound(WordArray) - indx
DoEvents
Next indx
End With

Is there a way I can have something pop up on screen saying "Macro running" and then disappear once the Macro is complete. Or, maybe someone has a better idea?

many thanks
Richi

 

[lambuhere1]

I think you can look at the progress bar in forms. It might be useful to meet your requirements.

 

[RobBroekhuis]

Combine lambuhere1's suggestion with a userform activated with

MyForm.show vbModeless

and you're well on your way to getting the behavior you're after. Alternatively, you could do all the processing inside your userform, and show it as a regular (modal) form.
Rob

 

[richiwatts]

They sound like good ideas but I am a bit of a beginner :-(

How would I change my code to make this work? An example would be great!

Many thanks!
Richi

 

[RobBroekhuis]

In the VBE environment, add a new userform to your project, which will bring up the graphical form editor, including the controls toolbox. If the toolbox doesn't show the progress bar control (it probably doesn't), right-click on empty space in the toolbox and choose "add additional controls", then check the box next to "Microsoft ProgressBar control". Now you have an icon in your toolbox for the progress bar, which you can drag onto your new form. Call your form "frmProgress" (change the .name property in the properties window in the lower left corner), and change other formatting as well, such as the window title etc. Name your progressbar control "pbProgress", by clicking it and changing its name property.
In your main macro use

frmProgress.show vbModeless
frmProgress.pbProgress=0

when you start your iterative code, and
frmProgress.pbProgress=(an expression that evaluates between 0 and 100)
inside your iterative code to display the progress.
At the end of your code, use

frmProgress.hide

to remove the form.
Rob

 

[richiwatts]

Hi Rob

Thanks for the help!

I have got the form set up OK but am totaly lost with putting the code into the Macro. Can I also delete the Statusbar line of my code now as it flashes like crazy when I run the Macro? Also I am not sure what the "iterative" code is and what to replace this with "=(an expression that evaluates between 0 and 100)"


How would I add this to that code that I have:

Sub ScanDocument()
   Dim oRg As Range
   Dim LineFromFile As String, LineString As String
   Dim path As String
   Dim WordArray As Variant
   Dim indx As Integer, LparenPos As Integer, RparenPos As Integer
   Dim WholeDocument As String
   
   Options.DefaultHighlightColorIndex = wdTurquoise
   If CheckExpiration = False Then Exit Sub

   WholeDocument = LCase$(ActiveDocument.Range.Text)

   path = ThisDocument.path
  Open path & Application.PathSeparator & "data.dat" For Input As #1
   Do While Not EOF(1)
        Line Input #1, LineString
        LparenPos = InStr(LineString, "(")
        Do While LparenPos > 0
            RparenPos = InStr(LparenPos, LineString, ")")
            If RparenPos > 0 Then
                LineString = Left(LineString, LparenPos - 2) & _
                    Right(LineString, Len(LineString) - RparenPos)
                LparenPos = InStr(LineString, "(")
            End If
        Loop
        LineFromFile = LineFromFile & vbTab & LineString
   Loop
   Close #1

   #If VB6 Then
   WordArray = Split(LineFromFile, vbTab)
   #Else
   WordArray = Word97Split(LineFromFile, vbTab)
   #End If

   Set oRg = ActiveDocument.Range

   Application.ScreenUpdating = False

   With oRg.Find
      .ClearFormatting
      .Replacement.ClearFormatting

      .Replacement.Highlight = True

      .Replacement.Text = &quot;^&&quot; ' <= code for &quot;found text&quot;
      .Forward = True
      .Wrap = wdFindContinue
      .Format = True
      .MatchCase = False
      .MatchWholeWord = True
      .MatchWildcards = False
      .MatchSoundsLike = False
      .MatchAllWordForms = False

      For indx = 1 To UBound(WordArray)
         If InStr(WholeDocument, LCase$(WordArray(indx))) > 0 Then
            .Text = WordArray(indx)
            .Execute Replace:=wdReplaceAll
         End If
         StatusBar = &quot;Scanning document, please wait =&quot; & UBound(WordArray) - indx
         DoEvents
      Next indx
   End With

   Application.ScreenUpdating = True
End Sub

I would be very grateful for further assistance!
Richi

 

[RobBroekhuis]

Yes, you can eliminate the statusbar code. Your "iterative code" is the for...next loop. As for the expression to assign to the progressbar, you could use

= 100*indx/ubound(wordarray)

Does that help?
Rob

 

[richiwatts]

I am getting an error!
The debuger is highlighting this line:
frmProgress.pbProgress = 100 * indx / UBound
I removed the spaces like the codee you provided but it keeps putting the spaces back.

the code now looks like this:

Loop
LineFromFile = LineFromFile & vbTab & LineString
frmProgress.Show vbModeless
frmProgress.pbProgress = 100 * indx / UBound(WordArray)

Loop
Close #1

Sorry for being a pain
Richi

 

[RobBroekhuis]

Does the variable indx have a value at the point where you put the code? The line

frmProgress.pbProgress = 100 * indx / UBound(WordArray)

should be in between your for and next statements. If that doesn't resolve the error, please let me know which error you're getting.
Rob

 

[richiwatts]

It was wrong and I have now put it in the right place. I am still getting an error:

run time error "6":
Overflow

This is how the code looks now:

frmProgress.Show vbModeless
frmProgress.pbProgress = 0

For indx = 1 To UBound(WordArray)
If InStr(WholeDocument, LCase$(WordArray(indx))) > 0 Then
      .Text = WordArray(indx)
      .Execute Replace:=wdReplaceAll
      End If
      DoEvents
    frmProgress.pbProgress = 100 * indx / UBound(WordArray)
      Next indx
   End With

Richi

 

[RobBroekhuis]

Sounds like a problem in the calculation. In debug mode, check the values of Ubound(wordarray) and indx when the error occurs. Almost sounds like Ubound(wordarray) is zero, but that doesn't make sense.
Rob

 

[Zathras]

It does make sense because Richi has the line of code up between the loops and not down inside the for. WordArray is still empty.

 

[richiwatts]

But I had changed that though to what the code is in my last post.

Richi

 

[Zathras]

Sorry, that's what I get for jumping in late.

BTW, I know that Excel 97 doesn't support modeless dialog boxes, and I would suppose that Word 97 doesn't either. (It's an enhancement that was provided in Office 2K.) I can't tell if that is a factor here, but I see reference to a function called Word97Split, so I offer it for consideration.

 

[richiwatts]

That hurt!

I can live without the progress bar if I have to but I would like a pop-up window saying something.

What can i use instead?

Richi!

 

[RobBroekhuis]

You can still use this approach, but you can't make the userform modeless. This means you'll have to use your userform (which you've already created) to do all your processing. A simple way to do this would be to put all the code that you've posted in the userform_activate event handler (to create this, double-click on an empty part of the userform in VBE, then select "activate" from the right dropdown box above the code window). At the end of the userform_activate sub, put

unload me

to remove the form. To start up your process, just use

frmProgress.show

If you'd like, give this a try and let us know if you run into problems.


Rob

 

[Zathras]

I'm more of an Excel type than Word, but just playing around, is is possible to use a new document for the progress display? In this example, I used the window title (caption), but you may be able to build on the concept. The called routine(s) should be able to update the ProgressWindow.Caption property. Anyway, for what it's worth:

==========================================================

Public ProgressWindow As Window

Sub test()

Set ProgressWindow = NewWindow
ProgressWindow.Caption = "Progress"
Application.Windows.Arrange (wdTiled)
ProgressWindow.DisplayRulers = False
ProgressWindow.Height = 0

ProgressWindow.Caption = "Processing 3 of 500"

MsgBox "Call subroutines from here"

ProgressWindow.Document.Saved = True
ProgressWindow.Close
Set ProgressWindow = Nothing

End Sub

 

[richiwatts]

Hi Rob!

I have tried what you said and it seems to work fine in Word 2000, I don't have Word 97 at home so I will have to wait to see if it still works.

Can you see any reason why it shouldn't?

Just to check I have done it right:

I have moved all the code from the Module "Sub ScanDocument()" to the form under "Private Sub UserForm_Activate()"

The code in the Module now looks like this:
Sub ScanDocument()
frmProgress.Show
End Sub

Does that sound right?

One thing that I am not sure about is the code still in the Module required for the Word 97 split:
Public Function Word97Split(ByVal sIn As String, Optional sDelim As _
String, Optional nLimit As Long = -1) As Variant
Can I leave this and the rest of its code in the Module or do I have to move that into the Form?

Thank you everybody for the help you have given me today!
Richi

 

[RobBroekhuis]

Yes, sounds like you've done the right thing. The split function can be in either location. If you need it for other processing, I'd leave it in the regular module rather than the userform module.
Let us know if it all works!
Rob

 

[combo]

The "overflow" error is probably caused by too big number in relation to its type. Probably indx is declared as Integer while UBound(WordArray) is greater than 32767.

Check the value:
Msgbox UBound(WordArray)
before starting the loop, next compare it with permitted values for the type of indx variable.
Try Long instead of integer.

Combo