Show count down during operation

[Rick S.]

Is there a way to show a "count down" like 1 of 100, 2 of 100, 3 of 100 etc.
in excel while excel is running a macro?
I am working in a folder with 100 files, everytime a file is opened by the
macro can a counter be shown what file it is one, IE 1 of 100 etc.

I looked at progress bars, but I don't think I can do that yet.
--
Regards

VBA.Newb.Confused
XP Pro
Office 2007

 

[Jim Thomlinson]

How about using the status bar, something like this...

Sub test()
Dim lng As Long

For lng = 1 To 10
Application.StatusBar = "Working on #" & lng & " of 10"
Application.Wait (Now() + TimeSerial(0, 0, 2))
Next lng
Application.StatusBar = False
End Sub

While this is running look at the status bar in the bottom left hand corner.

 

[Rick S.]

Your help got me to this, which works just fine!
Thanks Jim!
'======
Sub OpenAllWorkbooks2() 'open all workbooks in a folder location
Dim oFSO As Object
Dim vFileCount As Variant
Dim sCount As String
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim sFileName As String

Application.ScreenUpdating = False
Set oFSO = CreateObject("Scripting.FileSystemObject")
vFileCount = oFSO.GetFolder("M:\Qadocs\IPI'S\Test Folder\Run").Files.Count
Set Folder = oFSO.GetFolder("M:\Qadocs\IPI'S\Test Folder\Run") 'set
folder location here
sCount = vFileCount - 1
vFileCount = vFileCount - 1
'MsgBox sCount
For Each file In Folder.Files
sFileName = file
If Right(sFileName, 3) = "xls" Or _
Right(sFileName, 3) = "XLS" Or _
Right(sFileName, 4) = "xlsx" Then
'Workbooks.Open FileName:=file.Path
'code below is for passing workbook password on open event,
requires to arguments
Workbooks.Open FileName:=file.Path, Password:="2000",
WriteResPassword:="2000" 'pass workbook.open password
'<<<<< run macro here on Activeworkbook
Dim wkSheet As Worksheet
Dim i
For i = 4 To Worksheets.Count 'Ignore first three sheets
Application.DisplayAlerts = False
Sheets(i).Activate 'start with first IPI data sheet
ActiveSheet.Unprotect "2000"
Range("D4").Select
Selection.NumberFormat = "General"
ActiveCell.Formula =
"=MID(D6,4,FIND(""-"",D6)-4)&IF(RIGHT(D6)<>""1)"","" CONT"","""")"
Range("B6").Select
ActiveCell.FormulaR1C1 = "SHT"
Range("D6").Select
Selection.NumberFormat = "General"
ActiveCell.FormulaR1C1 = _

"=MID(CELL(""filename"",R[-5]C[-3]),SEARCH(""]"",CELL(""filename"",R[-5]C[-3]))+1,1024)"
Range("D10").Select
ActiveSheet.Protect "2000"
Sheets("Master Sheet").Select
Application.StatusBar = vFileCount & " of " & sCount
Next i
vFileCount = vFileCount - 1
Application.DisplayAlerts = True
Application.DisplayAlerts = True
ActiveWorkbook.Close SaveChanges:=True
End If
Next file
Set oFSO = Nothing
Application.ScreenUpdating = True
Application.StatusBar = ""
End Sub
'======
--
Regards

VBA.Newb.Confused
XP Pro
Office 2007