Macro Progress Indicator

[Telecorder]

Also, long loops should have a progress indicator and that can provide
an interval for the DoEvents function.

Jim- (Or other knowledgable entities...)
Was looking for such an animal to include in some code since my users will
be running the program on laptops/PCs that may have different processors
and/or
RAM which may be slower on older/less powerful systems.

I came across
http://spreadsheetpage.com/index.php/file/progress_indicator_demo/ that looks
promising but I'm unclear as to how to code the needed 'Main' sub logic for
updating.

My code on user-click searches a series of worksheet data tables for cell
entries >0; consolidates only the rows/columns associated with a cell entry

0 and

populates a summary worksheet with just those entries found. How could I tie
a Main sub logic that would is tied to the 'progress' of searching 13
worksheets for columns with value >0?

I saw where a "Kludge" might be used as if...

Start = Timer
Do while Time <start+0.1
DoEvents
Loop

But I'm really unsure of how to write the Sub Main code to incorporate the
above
SpreadsheetPageExcelTips approach with the "kludge" (Whatever that might
refer to...)

Insights, anyone?

 

[Gary''s Student]

This is kind of the opposite of:
Application.ScreenUpdating=False

Sub dural()
Application.Goto reference:=Range("A1")
For i = 1 To 10000
DoEvents
Range("A1").Value = Range("A1").Value + 1
Next
End Sub

You want the User to see the valuein A1 increase from 0 till 10,000

 

[Jim Cone]

The simplest solution is to show progress in the StatusBar.
Of course, users will have to know to look there.
Since you are searching 13 worksheets, something like this could suffice...
'--
For N = 1 to Worksheets.Count

'search code for each worksheet goes here

Application.StatusBar = "PROCESSING " & UCase$(Worksheets(N).Name)
'or... Application.StatusBar = "WORKSHEETS " & N
Next 'N

'After the loop completes...
Application.StatusBar = False
'--
Jim Cone
Portland, Oregon USA



"Telecorder"
<[email protected]>
wrote in message

Also, long loops should have a progress indicator and that can provide
an interval for the DoEvents function.

Jim- (Or other knowledgable entities...)
Was looking for such an animal to include in some code since my users will
be running the program on laptops/PCs that may have different processors
and/or
RAM which may be slower on older/less powerful systems.

I came across
http://spreadsheetpage.com/index.php/file/progress_indicator_demo/ that looks
promising but I'm unclear as to how to code the needed 'Main' sub logic for
updating.

My code on user-click searches a series of worksheet data tables for cell
entries >0; consolidates only the rows/columns associated with a cell entry

0 and

populates a summary worksheet with just those entries found. How could I tie
a Main sub logic that would is tied to the 'progress' of searching 13
worksheets for columns with value >0?

I saw where a "Kludge" might be used as if...

Start = Timer
Do while Time <start+0.1
DoEvents
Loop

But I'm really unsure of how to write the Sub Main code to incorporate the
above
SpreadsheetPageExcelTips approach with the "kludge" (Whatever that might
refer to...)

Insights, anyone?

 

[Telecorder]

Apologies - but ...I'm lost...
The following is the module code that I'm using upon Form Button Click to
generate the consolidated data to populate my Summary page (Users enter
quantities on 13 worksheets for various items and code brings over only those
rows with entered quantities.

Is there a way to generate a Progress Indicator Status tied to the ws search
of data field's? ie - How do I code the following to include Jim's Status Bar
progress approach, at the least, or how would I tie a separate UserForm bar
indicator approach activation to the Form Button click that activates the
following (and have it calculate completion %...)?

Many Thanks in advance...
___________________________________________________
Option Explicit

Sub CombineTs()
Dim rr As Integer 'row count of Summary
Dim n As Integer 'sheet count
Dim m As Integer 'column count Summary
Dim t As Integer
Dim s() As Variant 'row count others
Dim u As Integer 'cumulative row count
Dim ss As String 'name of Summary
Dim sc() As Variant 'column names in Summary
Dim ssc() As Variant 'column count others
Dim i As Integer
Dim j As Integer

ss = "Summary"

Application.ScreenUpdating = False
Application.DisplayAlerts = False

n = ActiveWorkbook.Worksheets.Count
ReDim s(n + 1)
ReDim ssc(n + 1)
u = 1
For t = 1 To n
If Not ss = Worksheets(t).Name And Worksheets(t).ListObjects.Count = 1 Then
s(t) = Worksheets(t).ListObjects(1).ListRows.Count
ssc(t) = Worksheets(t).ListObjects(1).ListColumns.Count
End If
Next t

With Sheets("Summary").ListObjects(1) 'Delete old data
.AutoFilter.ShowAllData
m = .ListColumns.Count
ReDim sc(m + 1)
For i = 1 To m
sc(i) = .ListColumns(i).Name
Next i
rr = .ListRows.Count
For t = 1 To rr - 1 'Delete all but one row
.ListRows(1).Delete
Next t
End With

For t = 1 To n
With Sheets(t)
If Not ss = .Name And .ListObjects.Count = 1 Then
For i = 1 To m
For j = 1 To ssc(t)
If sc(i) = .ListObjects(1).ListColumns(j).Name Then
.ListObjects(1).ListColumns(j).DataBodyRange.Copy _
Sheets("Summary").ListObjects(1).ListColumns(i).DataBodyRange.Cells(u)
End If
Next j
Next i
u = u + s(t)
End If
End With
Next t
With Sheets("Summary").ListObjects(1)

.Range.AutoFilter Field:=3, Criteria1:=">0"
End With
End Sub

 

[Jim Cone]

Maybe...
'Lines added in four places marked with '<<<<
'--
Sub CombineTs()
Dim rr As Integer 'row count of Summary
Dim n As Integer 'sheet count
Dim m As Integer 'column count Summary
Dim t As Integer
Dim s() As Variant 'row count others
Dim u As Integer 'cumulative row count
Dim ss As String 'name of Summary
Dim sc() As Variant 'column names in Summary
Dim ssc() As Variant 'column count others
Dim i As Integer
Dim j As Integer

ss = "Summary"
Application.ScreenUpdating = False
Application.DisplayAlerts = False

n = ActiveWorkbook.Worksheets.Count
ReDim s(n + 1)
ReDim ssc(n + 1)
u = 1
For t = 1 To n
If Not ss = Worksheets(t).Name And Worksheets(t).ListObjects.Count = 1 Then
s(t) = Worksheets(t).ListObjects(1).ListRows.Count
ssc(t) = Worksheets(t).ListObjects(1).ListColumns.Count
End If
Application.StatusBar = "Part One of Four " & Format$(t / n, "#00%") '<<<<
Next t

With Sheets("Summary").ListObjects(1) 'Delete old data
.AutoFilter.ShowAllData
m = .ListColumns.Count
ReDim sc(m + 1)
For i = 1 To m
sc(i) = .ListColumns(i).Name
Application.StatusBar = "Part Two " & Format$(i / m, "#00%") '<<<<
Next i

rr = .ListRows.Count
For t = 1 To rr - 1 'Delete all but one row
.ListRows(1).Delete
Application.StatusBar = "Part Three " & Format$(t / rr, "#00%") '<<<<
Next t
End With

For t = 1 To n
With Sheets(t)
If Not ss = .Name And .ListObjects.Count = 1 Then
For i = 1 To m
For j = 1 To ssc(t)
If sc(i) = .ListObjects(1).ListColumns(j).Name Then
.ListObjects(1).ListColumns(j).DataBodyRange.Copy _
Sheets("Summary").ListObjects(1).ListColumns(i).DataBodyRange.Cells(u)
End If
Next j
Next i
u = u + s(t)
End If
End With
Application.StatusBar = "Last Part " & Format$(t / n, "#00%") '<<<<
Next t
With Sheets("Summary").ListObjects(1)
.Range.AutoFilter Field:=3, Criteria1:=">0"
End With
End Sub
--
Jim Cone
Portland, Oregon USA



"Telecorder"
<[email protected]>
wrote in message
Apologies - but ...I'm lost...
The following is the module code that I'm using upon Form Button Click to
generate the consolidated data to populate my Summary page (Users enter
quantities on 13 worksheets for various items and code brings over only those
rows with entered quantities.

Is there a way to generate a Progress Indicator Status tied to the ws search
of data field's? ie - How do I code the following to include Jim's Status Bar
progress approach, at the least, or how would I tie a separate UserForm bar
indicator approach activation to the Form Button click that activates the
following (and have it calculate completion %...)?

Many Thanks in advance...
-snip-

 

[Jim Cone]

Plus...
....
Application.StatusBar = False
End Sub

 

[Telecorder]

Ya da man!

Many Thanks; Of course, a popup scrolling indicator bar would be showy and
nice but this does the trick to give indications that the progress is
progressing....

 

[Jim Cone]

You're welcome.
The flashy progress indicators all add some overhead to the process.
However, sometimes they are a better solution.
You also can simulate (somewhat) a progress bar within the status bar
by displaying consecutive characters (filled squares).
Jim Cone


"Telecorder" <[email protected]>
wrote in message
Ya da man!
Many Thanks; Of course, a popup scrolling indicator bar would be showy and
nice but this does the trick to give indications that the progress is
progressing....

 

[Telecorder]

You also can simulate (somewhat) a progress bar within the status bar
by displaying consecutive characters (filled squares).
Jim Cone

Curious... how would I code the status bar to reflect conscutive filled
squares in each... I assume one would modify the "#00%" portion of the
statusbar code but with what?

Application.StatusBar = "Part One of Four " & Format$(t / n, "#00%") '<<<<