inserting a progress bar

[sal21]

... how to inserting a progress bar during a macro in VBA for Excel?

 

[xlbo]

Have a look at the statusbar object as an alternative

eg

For i = 1 To 100
Application.StatusBar = i & "% Complete"

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)

Application.Wait waitTime
Next i
Application.StatusBar = False

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[macropod]

Hi Sal,

Check out:

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

Alternatively, you could use something like the following two routines:

Option Explicit
Dim SBar As Boolean

Private Sub MacroEntry()
SBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.ScreenUpdating = False
Application.Calculation = xlManual
End Sub

Private Sub MacroExit()
Application.Calculation = xlAutomatic
Application.StatusBar = False
Application.DisplayStatusBar = SBar
Application.ScreenUpdating = True
End Sub

which you would call from a macro like:

Sub TrimRange()
Call MacroEntry
On Error Resume Next
Dim Cell As Range
Dim CellCount As Long
Dim Percent As Integer
Dim I As Long
I = 0
If Selection.Rows.Count * Selection.Columns.Count > 1 Then
CellCount = Selection.Rows.Count * Selection.Columns.Count
Else
CellCount = ActiveSheet.UsedRange.Rows.Count * ActiveSheet.UsedRange.Columns.Count
End If
For Each Cell In Selection.SpecialCells(xlConstants)
Cell.Replace What:=Chr(160), Replacement:=Chr(32)
Cell.Value = Application.Trim(Cell.Value)
I = I + 1
If Int(I / CellCount * 100 + 0.5) = Percent + 1 Then
Percent = Percent + 1
Application.StatusBar = Percent & "% Trimmed"
End If
Next Cell
Call MacroExit
End Sub

I posted something similar here a few days ago.

Cheers