Hi all,

Could anyone help me how to implement the progress bar to indicate the Marco
from start to end?

I want to let the user know the macro is running and not is halt. I found
some related information. However, it counts the selection.cells.count to
implement.

In my macro, I already need to select all cells for doing work. Therefore it
is not so suitable in my case.

Thanks for in advance.

Tlee

Hi,
The code as follow was published in the comunity hope will help you as well

Sub StatusProgressBar(lCounter As Long, _
lMax As Long, _
bReset As Boolean, _
Optional lInterval As Long = 1, _
Optional strText As String, _
Optional lLength As Long = 100)

'lCounter the loop counter passed from the procedure
'lMax the maximum of the loop counter
'bReset do this at the very first iteration, eg i = 0
'lInterval the update interval of the statusbar
'strText any text preceding the progressbar
'lLength lenght in characters of the progressbar
'---------------------------------------------------------
Dim lStripes As Long
Static lLenText As Long
Static strBuffer As String
Static lOldStripes As Long

If bReset Then
lLenText = Len(strText)
strBuffer = strText
strBuffer = strBuffer & String(lLength, ".")
strBuffer = strBuffer & "|"
lOldStripes = 0
End If

If lCounter Mod lInterval = 0 Or lCounter = lMax Then
lStripes = Round((lCounter / lMax) * lLength, 0)
If lStripes - lOldStripes 0 Then
Mid$(strBuffer, lLenText + 1 + lOldStripes) = "|"
lOldStripes = lStripes
Application.StatusBar = strBuffer
End If
End If

End Sub

Sub test()

Dim i As Long
Dim strText As String

strText = " Looping through the range - "

For i = 1 To 6000000
StatusProgressBar i, 6000000, i = 1, 6000000 \ 200, strText
Next i

End Sub

Take a look to these webs

http://support.microsoft.com/kb/211736
http://j-walk.com/ss/excel/tips/tip34.htm
status bar....
http://www.cpearson.com/excel/StatusBar.htm

If this helped please click yes, thanks

Eduardo,

Thanks for your information. I took a look of example already.

However, it seems use loops to filling number into cells or select the
numbers of cells for counting the progress.

Anyone know how to counting with steps of my code running to display
progress?

Thanks

Tlee


Hi,
The code as follow was published in the comunity hope will help you as
well

Sub StatusProgressBar(lCounter As Long, _
lMax As Long, _
bReset As Boolean, _
Optional lInterval As Long = 1, _
Optional strText As String, _
Optional lLength As Long = 100)

'lCounter the loop counter passed from the procedure
'lMax the maximum of the loop counter
'bReset do this at the very first iteration, eg i = 0
'lInterval the update interval of the statusbar
'strText any text preceding the progressbar
'lLength lenght in characters of the progressbar
'---------------------------------------------------------
Dim lStripes As Long
Static lLenText As Long
Static strBuffer As String
Static lOldStripes As Long

If bReset Then
lLenText = Len(strText)
strBuffer = strText
strBuffer = strBuffer & String(lLength, ".")
strBuffer = strBuffer & "|"
lOldStripes = 0
End If

If lCounter Mod lInterval = 0 Or lCounter = lMax Then
lStripes = Round((lCounter / lMax) * lLength, 0)
If lStripes - lOldStripes 0 Then
Mid$(strBuffer, lLenText + 1 + lOldStripes) = "|"
lOldStripes = lStripes
Application.StatusBar = strBuffer
End If
End If

End Sub

Sub test()

Dim i As Long
Dim strText As String

strText = " Looping through the range - "

For i = 1 To 6000000
StatusProgressBar i, 6000000, i = 1, 6000000 \ 200, strText
Next i

End Sub

Take a look to these webs

http://support.microsoft.com/kb/211736
http://j-walk.com/ss/excel/tips/tip34.htm
status bar....
http://www.cpearson.com/excel/StatusBar.htm

If this helped please click yes, thanks

add
Application.StatusBar = "{something}"
at various parts of your code


"tlee" wrote in message
...
Eduardo,

Thanks for your information. I took a look of example already.

However, it seems use loops to filling number into cells or select the
numbers of cells for counting the progress.

Anyone know how to counting with steps of my code running to display
progress?

Thanks

Tlee


Hi,
The code as follow was published in the comunity hope will help you as
well

Sub StatusProgressBar(lCounter As Long, _
lMax As Long, _
bReset As Boolean, _
Optional lInterval As Long = 1, _
Optional strText As String, _
Optional lLength As Long = 100)

'lCounter the loop counter passed from the procedure
'lMax the maximum of the loop counter
'bReset do this at the very first iteration, eg i = 0
'lInterval the update interval of the statusbar
'strText any text preceding the progressbar
'lLength lenght in characters of the progressbar
'---------------------------------------------------------
Dim lStripes As Long
Static lLenText As Long
Static strBuffer As String
Static lOldStripes As Long

If bReset Then
lLenText = Len(strText)
strBuffer = strText
strBuffer = strBuffer & String(lLength, ".")
strBuffer = strBuffer & "|"
lOldStripes = 0
End If

If lCounter Mod lInterval = 0 Or lCounter = lMax Then
lStripes = Round((lCounter / lMax) * lLength, 0)
If lStripes - lOldStripes 0 Then
Mid$(strBuffer, lLenText + 1 + lOldStripes) = "|"
lOldStripes = lStripes
Application.StatusBar = strBuffer
End If
End If

End Sub

Sub test()

Dim i As Long
Dim strText As String

strText = " Looping through the range - "

For i = 1 To 6000000
StatusProgressBar i, 6000000, i = 1, 6000000 \ 200, strText
Next i

End Sub

Take a look to these webs

http://support.microsoft.com/kb/211736
http://j-walk.com/ss/excel/tips/tip34.htm
status bar....
http://www.cpearson.com/excel/StatusBar.htm

If this helped please click yes, thanks

Hi Patrick,

Thanks for you message. but I the progress bar is more attractive to user.

So, in the other application, such that doing files zip in Winzip program,
how does its progress bar work?

Does it count the proessing time?

Thanks,

Tlee

add
Application.StatusBar = "{something}"
at various parts of your code


"tlee" wrote in message
...
Eduardo,

Thanks for your information. I took a look of example already.

However, it seems use loops to filling number into cells or select the
numbers of cells for counting the progress.

Anyone know how to counting with steps of my code running to display
progress?

Thanks

Tlee


Hi,
The code as follow was published in the comunity hope will help you as
well

Sub StatusProgressBar(lCounter As Long, _
lMax As Long, _
bReset As Boolean, _
Optional lInterval As Long = 1, _
Optional strText As String, _
Optional lLength As Long = 100)

'lCounter the loop counter passed from the procedure
'lMax the maximum of the loop counter
'bReset do this at the very first iteration, eg i = 0
'lInterval the update interval of the statusbar
'strText any text preceding the progressbar
'lLength lenght in characters of the progressbar
'---------------------------------------------------------
Dim lStripes As Long
Static lLenText As Long
Static strBuffer As String
Static lOldStripes As Long

If bReset Then
lLenText = Len(strText)
strBuffer = strText
strBuffer = strBuffer & String(lLength, ".")
strBuffer = strBuffer & "|"
lOldStripes = 0
End If

If lCounter Mod lInterval = 0 Or lCounter = lMax Then
lStripes = Round((lCounter / lMax) * lLength, 0)
If lStripes - lOldStripes 0 Then
Mid$(strBuffer, lLenText + 1 + lOldStripes) = "|"
lOldStripes = lStripes
Application.StatusBar = strBuffer
End If
End If

End Sub

Sub test()

Dim i As Long
Dim strText As String

strText = " Looping through the range - "

For i = 1 To 6000000
StatusProgressBar i, 6000000, i = 1, 6000000 \ 200, strText
Next i

End Sub

Take a look to these webs

http://support.microsoft.com/kb/211736
http://j-walk.com/ss/excel/tips/tip34.htm
status bar....
http://www.cpearson.com/excel/StatusBar.htm

If this helped please click yes, thanks

these progress bars are working based off time elapsed and amount processed
as a %age total size.

where to have a series of procedures, you need to guestimate the time for
each to get a total estimated time, then your progress bar will run. be sure
though that your timings get refreshed. Nothing worse than to tell the user
15 seconds and then nothing happens for a minute


"tlee" wrote in message
...
Hi Patrick,

Thanks for you message. but I the progress bar is more attractive to user.

So, in the other application, such that doing files zip in Winzip program,
how does its progress bar work?

Does it count the proessing time?

Thanks,

Tlee

add
Application.StatusBar = "{something}"
at various parts of your code


"tlee" wrote in message
...
Eduardo,

Thanks for your information. I took a look of example already.

However, it seems use loops to filling number into cells or select the
numbers of cells for counting the progress.

Anyone know how to counting with steps of my code running to display
progress?

Thanks

Tlee


Hi,
The code as follow was published in the comunity hope will help you as
well

Sub StatusProgressBar(lCounter As Long, _
lMax As Long, _
bReset As Boolean, _
Optional lInterval As Long = 1, _
Optional strText As String, _
Optional lLength As Long = 100)

'lCounter the loop counter passed from the procedure
'lMax the maximum of the loop counter
'bReset do this at the very first iteration, eg i = 0
'lInterval the update interval of the statusbar
'strText any text preceding the progressbar
'lLength lenght in characters of the progressbar
'---------------------------------------------------------
Dim lStripes As Long
Static lLenText As Long
Static strBuffer As String
Static lOldStripes As Long

If bReset Then
lLenText = Len(strText)
strBuffer = strText
strBuffer = strBuffer & String(lLength, ".")
strBuffer = strBuffer & "|"
lOldStripes = 0
End If

If lCounter Mod lInterval = 0 Or lCounter = lMax Then
lStripes = Round((lCounter / lMax) * lLength, 0)
If lStripes - lOldStripes 0 Then
Mid$(strBuffer, lLenText + 1 + lOldStripes) = "|"
lOldStripes = lStripes
Application.StatusBar = strBuffer
End If
End If

End Sub

Sub test()

Dim i As Long
Dim strText As String

strText = " Looping through the range - "

For i = 1 To 6000000
StatusProgressBar i, 6000000, i = 1, 6000000 \ 200, strText
Next i

End Sub

Take a look to these webs

http://support.microsoft.com/kb/211736
http://j-walk.com/ss/excel/tips/tip34.htm
status bar....
http://www.cpearson.com/excel/StatusBar.htm

If this helped please click yes, thanks