Question
August 16, 2007 at 10:05 AM
pjl_76

Visual Basic User Form macro in Excel (progress indicator)

by pjl_76 . Updated 19 years, 2 months ago

I found some code that showed how to use a User form with a list box to show the progress of macro. I can make a macro that works great by itself, but when i try to use the same lines of code in an existing macro it will not work.. This is the test macro I made, that works great.

Sub ProgressTest()

Dim ufUpdate As Progress
Dim dtTime As Date
Dim ListOneValue As Integer
Dim ListOneUpdate As String
Dim ListTwoUpdate As String

Set ufUpdate = New Progress

ufUpdate.Show
ufUpdate.lbxStatus.AddItem “Starting Test…”

‘ delay for 1 second
dtTime = Now
Do: DoEvents: Loop Until Now > dtTime + TimeValue(“00:00:01”)

ufUpdate.lbxStatus.AddItem “Locating Duplicate Menu Items…”

For I = 1 To 3

‘ delay for 1 seconds
dtTime = Now
Do: DoEvents: Loop Until Now > dtTime + TimeValue(“00:00:01”)

ListOneValue = I
ListOneUpdate = “Locating Duplicate Menu Items… ” & ListOneValue & ” Found…”
ufUpdate.lbxStatus.List(1) = ListOneUpdate
Next I

‘ delay for 1 seconds
dtTime = Now
Do: DoEvents: Loop Until Now > dtTime + TimeValue(“00:00:01”)

ListOneUpdate = ListOneUpdate & “Done”
ufUpdate.lbxStatus.List(1) = ListOneUpdate

ListTwoUpdate = “Adding Periods…”
ufUpdate.lbxStatus.AddItem ListTwoUpdate

For I = 1 To 5
dtTime = Now

Do: DoEvents: Loop Until Now > dtTime + TimeValue(“00:00:01”)

ListTwoUpdate = ListTwoUpdate & “.”
ufUpdate.lbxStatus.List(2) = ListTwoUpdate
Next I

‘ delay
dtTime = Now
Do: DoEvents: Loop Until Now > dtTime + TimeValue(“00:00:01”)

ListTwoUpdate = ListTwoUpdate & “Done”
ufUpdate.lbxStatus.List(2) = ListTwoUpdate

dtTime = Now
Do: DoEvents: Loop Until Now > dtTime + TimeValue(“00:00:01”)

Unload ufUpdate

Set ufUpdate = Nothing

End Sub

ok, now I placed these same lines throughout the intened macro and it will not work. The user form will popup with a blank list box and that is all I get. Please help.. Here is the fist bit of code from the target macro..

Sub BuildArray()

Dim ufUpdate As Progress
Dim rngIn As Range
Dim MenuItemNumArr() As Variant
Dim iListCount As Integer
Dim iCtr As Integer

‘instantiate the progress userform
Set ufUpdate = New Progress
ufUpdate.Show
‘display a step in progress listbox
ufUpdate.lbxStatus.AddItem “Starting Process…”

‘ Turn off screen updating to speed up macro.
Worksheets(“Weekly Totals”).Unprotect
Range(“F2:H500”).Value = “”
Application.ScreenUpdating = False

…….more code
End Sub

It is the same whether screen updating is on or off…

any input would be MUCH appretiated..

This discussion is locked

All Comments