I have a need to utilize a progress indicator which is time based.
Converting ascii "text" files to spreadsheets takes appx 5 min per
file. The conversion process can be for 1 or more files (up to 5). At
start time the number of files is determined. If processing 1 file
only the run time = 5 min, if 2 files run time = 10 min, etc. I would
like to display a progress indicator utilizing the system clock to
reflect the time left.

I have a UserForm "fmProgress" which has 2 "stacked" labels wiith
different colors.... That's as far as I can go! I need help with
writing code to make the top should grow (left to right) to cover the
bottom as a way to reflect the progress. I am also in the dark when
it comes to working with the system clock and using it to increment the
growth of the top label. If you have example code constructed (or
could construct same) I would greatly appreciate it!

I know that some of you have "been there, done that", hence I ask for
your assistance. Thank you very much.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Best to ask the "Excel pro".

You designed the form, so there is no way for anyone to know what is red and
what is blue, what you are trying to do or why it doesn't appear as you
wish. Try throwing in a Doevents after the call to the initialize and
update code.

whenever you call the update routine, it should adjust the progress in
proportion to the elapsed time relative some guessed at total time
This certainly is not time based - it isn't periodic with respect to time -
it is event based - called after the opening of each file. So you are
making an assumption on how long it will take to open all the files.

--
Regards,
Tom Ogilvy


mcdowell wrote in message
...
The below Code comes from an Excel pro. It seems to run OK, however the
Userform "fmProgress" is a blank white form instead of the red label
over the blue label as called for in the form design. All the code is
in the Form module. I have the Public Sub UpdateProgress set to Public
in order to call in in the processing macro using
fmProgress.UpdateProgress. Anyone have any suggestions as to what the
problem is??

Option Explicit
Private iNumberOfFiles As Single
Private dStartTime As Date
Private lngTwipPerSec As Long

Public Sub UpdateProgress()
On Error Resume Next ' Error would be in size of label

Dim lng As Long

lng = lngTwipPerSec * DateDiff("s", dStartTime, Time)
' Move the lables
lblTimeRemaining.Move lng, 0, Me.Width - lng ' - 150
lblTimeElapsed.Move 0, 0, lng
End Sub

Private Sub InitSetup()
Dim i As Integer
Dim i2 As Integer

' Set the number of files
iNumberOfFiles = Worksheets("Sort").Range("A17").Value

' Set the sizes & Move the lables
lblTimeElapsed.Move Me.Left, 0, 0, Me.Height
lblTimeRemaining.Move Me.Left, 0, Me.Width, Me.Height

' Set the Start Time
dStartTime = Time

' Set the twip per second
lngTwipPerSec = Me.Width / (iNumberOfFiles * 5 * 60)

End Sub



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Did you try the progress indicator example from JWalk?

http://j-walk.com/ss/excel/tips/tip34.htm


it uses a form and labels. change the code to suit your needs.



hope it helps

"mcdowell" wrote in message
...
I have a need to utilize a progress indicator which is time based.
Converting ascii "text" files to spreadsheets takes appx 5 min per
file. The conversion process can be for 1 or more files (up to 5). At
start time the number of files is determined. If processing 1 file
only the run time = 5 min, if 2 files run time = 10 min, etc. I would
like to display a progress indicator utilizing the system clock to
reflect the time left.

I have a UserForm "fmProgress" which has 2 "stacked" labels wiith
different colors.... That's as far as I can go! I need help with
writing code to make the top should grow (left to right) to cover the
bottom as a way to reflect the progress. I am also in the dark when
it comes to working with the system clock and using it to increment the
growth of the top label. If you have example code constructed (or
could construct same) I would greatly appreciate it!

I know that some of you have "been there, done that", hence I ask for
your assistance. Thank you very much.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/