Progress Bar Macro Incorporated Into MyMacro

[ryguy7272]

I am trying to get the Progress Bar macro incorporated into a macro that I
use to create and format a report for work. I am not sure where to put the
code for the macro that I developed because it seems like

The Progress Bar macro is here:
http://support.microsoft.com/kb/211736

I though I could place my code into the area after “Call Mainâ€
Private Sub UserForm_Activate()
' Set the width of the progress bar to 0.
UserForm1.LabelProgress.Width = 0

' Call the main subroutine.
Call Main
Call MyMacro
End Sub

But it doesn’t seem like I can split the time between the Progress Bar and
my own macro (MyMacro). One sub runs and the other runs (based on whether I
arrange the code order as Call Main and then Call MyMacro or Call MyMacro and
thenCall Main). Also, how would I get the Progress Bar to progress based on
the time duration of MyMacro (this changes)?

Right now I use a timer and a MessageBox:
Sub MyMacro
Dim sngStart As Double
sngStart = Now
'code here
'...etc
MsgBox "Process Complete!! " & Counter & _
" File Updated" & vbNewLine & _
" took " & Format(Now - sngStart, "hh:mm:ss")
End Sub



Regards,
Ryan---

 

[Sean]

I am trying to get the Progress Bar macro incorporated into a macro that I
use to create and format a report for work. I am not sure where to put the
code for the macro that I developed because it seems like

The Progress Bar macro is here:http://support.microsoft.com/kb/211736

I though I could place my code into the area after "Call Main"
Private Sub UserForm_Activate()
' Set the width of the progress bar to 0.
UserForm1.LabelProgress.Width = 0

' Call the main subroutine.
Call Main
Call MyMacro
End Sub

But it doesn't seem like I can split the time between the Progress Bar and
my own macro (MyMacro). One sub runs and the other runs (based on whether I
arrange the code order as Call Main and then Call MyMacro or Call MyMacro and
thenCall Main). Also, how would I get the Progress Bar to progress based on
the time duration of MyMacro (this changes)?

Right now I use a timer and a MessageBox:
Sub MyMacro
Dim sngStart As Double
sngStart = Now
'code here
'...etc
MsgBox "Process Complete!! " & Counter & _
" File Updated" & vbNewLine & _
" took " & Format(Now - sngStart, "hh:mm:ss")
End Sub

Regards,
Ryan---

I got this working pretty well, I'll see if I can get a sample piece
of code.

 

[Sean]

I got this working pretty well, I'll see if I can get a sample piece
of code.- Hide quoted text -

- Show quoted text -

I have a sample fle which I got on this board, you can see the code
behind it and a working example, if you want it just e-mail me. You
should be able to export the form and the class module from this file,
author of this example is Robin Hammond, perhaps just do a search on
this name and you may find it also

 

[FSt1]

hi
i think you need to go back a read the article again. you are suppose to
create a userform to display the status bar, put the code in the user form
then call the user form to run the status bar.
while your at it. check this out as an alternative
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q103404
how to use the status bar.

Regards
FSt1

 

[FSt1]

oh yes...note this too.
the code you are looking at on the web site(main) is do nothing code that
only generates random numbers and the progress meter measures it's progress.
you need to replace that part of the progress meter code (the loop part)
with your code so that the progress meter is measuring what you are doing. in
other words, build your code inside the progress meter code.

regards
FSt1

 

[Tim879]

Based on the comment above from Sean...

The website he was referring to is http://www.enhanceddatasystems.com/ED/Pages/ExcelProgressBar.htm

I downloaded the files yesterday and I must admit, they worked like a
charm and were extremely easy to use.

 

[ryguy7272]

Thanks Tim879! Well, I thought I was getting it, but I guess I’m not.
Please notice, I am now using the code from the Enhanced Datasystems Site.
This seems as good as the last macro from the MS site. I can’t seem to
figure out how the macro will determine how long it will take to run through
the code, and then control the speed of the progress bar based on that
duration. Am I just not putting my code within the right part of the loop or
what?

Notice, my code lies between the two lines of *******. My code does exactly
what I need it to (just copying, pasting, sorting, etc.). I just can’t
figure out how to run the macro for the progress bar, and run my code,
simultaneously.

My code is shown below:
Sub ProgBarDemo()
Dim PB As clsProgBar
Dim nCounter As Integer
Dim lWaitCount As Long

Set PB = New clsProgBar

With PB
.Title = "Progress Bar"
.Show

For nCounter = 0 To 100
.Progress = nCounter
.Caption1 = "Progress %" & CStr(nCounter)
For lWaitCount = 0 To 1000000
If UserCancelled = True Then GoTo EndRoutine

'********************************************************************
Application.ScreenUpdating = False
Dim sh As Worksheet
Dim sht As Worksheet

Dim sngStart As Double
sngStart = Now

Application.DisplayAlerts = False
For Each sh In ActiveWorkbook.Worksheets
If InStr(1, sh.Name, "REPORT - INTL vs. US") Then
sh.Delete
End If
Next sh
For Each sht In ActiveWorkbook.Worksheets
If InStr(1, sht.Name, "REPORT") Then
sht.Delete
End If
Next sht
Application.DisplayAlerts = True

Worksheets.Add.Name = "REPORT"
Worksheets.Add.Name = "REPORT - INTL vs. US"

Sheets("Main").Select
Rows("1:1").Select
Selection.AutoFilter
Range("P1").Select
Selection.AutoFilter Field:=15, Criteria1:="ACTIVE"
Cells.Select
Selection.Copy
Sheets("REPORT").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


Range("D:I,M:M,P:R,T:U,X:Y,ABA").Select
Range("AB1").Activate
Selection.Delete shift:=xlToLeft

Rows("1:1").Select
Selection.AutoFilter

Selection.AutoFilter Field:=10, Criteria1:="=INT", Operator:=xlOr, _
Criteria2:="=US"
Selection.AutoFilter Field:=11, Criteria1:="<D", Operator:=xlAnd
Selection.AutoFilter Field:=7, Criteria1:="<>REOPENED", Operator:=xlAnd

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("REPORT").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("B:B,I:I").Select
Range("I1").Activate
Application.CutCopyMode = False
Selection.NumberFormat = "[$-409]d-mmm-yy;@"
Columns("L:M").Select
Selection.Style = "Currency"
Range("A1").Select

Rows("1:1").Select
With Selection.Interior
.ColorIndex = 11
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Range("A1:M9").Sort Key1:=Range("J2"), Order1:=xlAscending, Key2:=Range( _
"K2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
Range("A1").Select

Cells.Select
Selection.Subtotal GroupBy:=11, Function:=xlSum,
TotalList:=Array(12, 13) _
, Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Range("A1").Select

Application.ScreenUpdating = True

'********************************************************************
Next lWaitCount
Next nCounter

EndRoutine:

.Finish

End With

Set PB = Nothing

End Sub

Thanks!!
Ryan--