# Showing Progress for Multi-Stage Processes



## Greg Truby (May 4, 2005)

Howdy folks, 

I'm in the middle of a rather large (months-long) project, so I haven’t spent much time on the boards lately.  One aspect of many projects is keeping the user informed of progress while processes are executing.  Quite a while back I had written my own version of Walkenbach’s userform-with-label as progress indicator.  Basically, I'd added more bars (labels if you want to be technical) to Walkenbach's form so that I could track progress on a half-dozen major processes as well as having a bar for sub-processes.  However, I found adapting my multi-bar form to use in other projects was cumbersome.  While trying to recycle it into my current project I got fed up enough with it to try using a UserForm as a class module (I remember reading somewhere that this is really what they are…) Anyhow, it seems to work pretty well as a class module.  And now I've got a progress-indicator tool that's pretty easy to drop into new projects.

So, I've cleaned it up reasonably well [500+ lines of code (thus far)] and would be willing to share it if'n it'd be of interest to anyone.  So anyone interested?


----------



## Glaswegian (May 27, 2005)

Hi Greg

Apologies for not replying sooner, some **** nuisance got in the way ("work" someone called it - never heard of it myself ).

I am mightily (is that a word?) impressed - it works superbly!  It is also a good opportunity for me to see a Class Module in action, as it were.  I'm not very good on Class Modules so it will probably take me a while to fully understand everything you've done.  I think I understand most of the rest - I've been stepping through the code several times, just to watch the form and bars being drawn.

I probably won't need to use all of it but I'm going to try and squeeze in some bits to at least one of the things I'm working on just now.

Anyway, great job - and thanks for sharing!!

Regards


----------



## Greg Truby (May 27, 2005)

Iain,

Thanks for the positive feedback.  Yes, it should serve as a pretty good example of a class module as well as showing how to add controls to a user form at run time.  But the point of the whole exercise was to create a tool that I could quickly import into a project and utilize with minimal additional work.  Just a quick study of the simple example code I included should be all you'd really need to know before you could use it.

Anyway, enjoy and thanks for the feedback.  If you have an idea on how to improve it, just post or send me a PM.

Regards,


----------



## ashwalker (Aug 19, 2005)

*Interested in code*

I'm new to VBA, right now I am just enhancing codes created by others. One of the codes has several steps, and takes awhile to complete, and I thought it would be good to add a progress indicator to show the user it is running, I did add a msg box letting them know that it is processing in the background, but would rather have the progress indicator. I have been looking at other codes on how to do this, but having a hard time taking it from the examples into the actual code I am using. Also I would like to have a way of being able to copy the code into any macros I might work on in the future. And that is exactly what it sounds like you can do with your code. Can you please post it

Thanks


----------



## Smitty (Aug 19, 2005)

Welcome to the Board!

Unfortunately, it's a lot of code (and a lot of effort on Greg's part), and probably just a wee bit much to post here and try to keep it organized (and explain what goes where).  If you've just started with VBA, it would be a lot to digest and even make work right as a sample, let alone specific to your needs.

However, Greg did an excellent job of commenting it, and as he did offer to e-mail it to anyone who asked (and I don't see him around), I'll send it to you if you PM me your e-mail address.

Smitty

How long does your code run anyway?  You might want to post it in the Excel forum for some tips on how to speed it up.   :wink:


----------



## Greg Truby (Aug 22, 2005)

Ashwalker,

Welcome to the board.  Did you PM Smitty and get a copy of the Progress Indicator workbook?  If not, feel free to PM me with your e-mail address and I'll be happy to send it to you.

While it is true that the UserForm's code module itself contains a fair bit of code, I wouldn't let that scare me off.  If I've programmed this thing reasonably well, you _should_ be able to take the "black box" approach.  I.e. you don't necessarily have to understand the code behind something in order to be able to use it.  For example, Ole Erlandsen, Jim Rech and David Wiseman put together a class module for a status bar LED progress indicator (if you want to see that one, follow this link http://j-walk.com/ss/excel/files/developer.htm and scroll down to the section labeled "Control the LED Display in the StatusBar" ).  I was able to plug Ole's class module into my workbooks and get it to work properly several years ago; when my understanding of VBA was a fraction of what it is today. (Even today I cannot claim to fully understand every line of code in that thing.)  But later, after I knew more VBA, I went back and stepped through the code, studying how it was organized and I learned a lot about class modules by doing so.  I'm a long way from being an expert in them (class modules), but at least I've gotten my feet wet a few times.  My point is, you've got nothing to lose by trying it out!   Worst case, ya can't use it.  Best case, ya can use it and you might learn something (er - maybe; no guarantees -  ).

And, as Smitty has already said; if you have a hunch that you might be able to speed up your code; feel free to post it in the questions forum to see if anyone has suggestions on how to streamline it. 

Regards,


----------



## ashwalker (Aug 22, 2005)

It depends on a few variables. Hundreds of people currently use this Excel sheet, and it is different for everyone as it depends on how much information they need extracted out of an Access Database. And how many reports they need created based on the information they extracted. So it varies, it could take a couple of minutes for one person, but than it could take 15 minutes or more for another person. The code is huge

thanks for your help guys, these message boards will provide me with a lot of useful information, thanks again


----------



## Greg Truby (Apr 25, 2006)

At least until I get so fed up with Roadrunner's neanderthal home page policies and switch ISP's this link should work as a way to download a sample of this in the off chance that anyone is interested...

http://home.kc.rr.com/trubys/ExcelFiles/ProgressIndicatorUserFormasClassModulev2.xls


----------



## Greg Truby (May 1, 2006)

_Over the weekend I received a PM from powerpackinduo regarding how to use this.  I suggested that I go ahead and post the contents here so that anyone else that downloads the sample workbook can see another example of how to impliment it.  So here is the content of powerpackinduo's initial PM to me:_

I see you've written some code on adding a progress indicator to a macro and even keep track of sub processes within the macro. I'd love to add this to my sheet. Right now the sheet seems to freeze up because the amount of data it processes. I've posted the code to the board to get suggestions on speeding it up but have had no luck on the sub process section. I have had help in speeding up the rest and included all of their comments. I'm hoping since I can't speed up the subprocess I can give them an idea of how much is left to do. I even saw that there might a way to show how much "Time" is left to. That would be awesome too but I don't want to ask for too much. 
I tried going through the post: «link to this thread» and downloading the sheet but that seems too far for me to comprehend adding this to my little bit of code. 
Here is my code: 

```
Private Sub CommandButton1_Click() 
'      Compare 2005 and 2006 and transfers all people who have 
'       not reserved in 2006 and put them on a separate sheet. 
Dim lRow As Long 
Dim R As Range, rData As Range 
Dim sCur As String 
Dim WS1 As Worksheet, WS2 As Worksheet 
Dim wsNotReserved As Worksheet, ws As Worksheet 
Dim LastRow As Long 

Application.ScreenUpdating = False 

Set WS1 = Sheets("2005") 
Set WS2 = Sheets("2006") 
Set rData = WS2.Range("C2:C" & WS2.Cells(Rows.Count, "C").End(xlUp).Row) 

' Check whether macro has already produced the 
' Not Reserved sheet and delete it. 

On Error Resume Next 'if error occurs, continue to next line of code 
'attempt to set ws variable 
Set wsNotReserved = Sheets("Not Reserved") 
'if sheet already exists, no error will occur 
If Err = 0 Then 'if sheet exists 
    'delete Not Reserved sheet 
    Application.DisplayAlerts = False 
    wsNotReserved.Delete 
    Application.DisplayAlerts = True 
    On Error GoTo 0 'reset error trapping 
    With WS2 
        'only selecting sheet to remove freeze panes 
        .Select 
        ActiveWindow.FreezePanes = False 
        .Cells.EntireColumn.Hidden = False 
        On Error Resume Next 
        .Cells.AutoFilter 
        On Error GoTo 0 'reset error trapping 
        .Columns("T:AE").Delete 
        .Rows("1:1").Delete 
    End With 
End If 
On Error GoTo 0 'reset error trapping 

'if sheet does *not* already exist, code continues here 
' Insert sheet for Not Reserved 
    Sheets.Add 
    ActiveSheet.Select 
    ActiveSheet.Move After:=Sheets(3) 
    ActiveSheet.Name = "Not Reserved" 


Set wsNotReserved = Sheets("Not Reserved") 
'since the Not Reserved ws is being deleted and/or added as a new *blank* sheet 
'every time, this statement will always result in lRow being set as 1 
'You can just hard-code this value as 1 to save a tiny bit of time 
lRow = 1 
'lRow = wsNotReserved.Cells(Rows.Count, "C").End(xlUp).Row 

'  **** SUBPROCCESS OF COMPARING SHEETS AND PLACING ON 
' A NEW SHEET.  I WOULD LIKE TO TRACK THIS IN IT'S OWN 
' PROGRESS BAR. 
For Each R In WS1.Range("C2:C" & WS1.Cells(Rows.Count, "C").End(xlUp).Row) 
    sCur = R.Text 
    If Application.CountIf(rData, sCur) = 0 Then 
        lRow = lRow + 1 
        wsNotReserved.Rows(lRow).Value = WS1.Rows(R.Row).Value 
    End If 
Next R 
' **** END OF SUBPROCESS 

'can loop so sheets/ranges do not have to be selected 
For Each ws In Sheets(Array(WS2.Name, wsNotReserved.Name)) 
    With ws 
        .Columns("T:AE").Insert Shift:=xlToRight 
        'enter the first value in T1 
        .Range("T1") = "10" 
        'use Fill Series to enter numbers in the rest of the range 
        .Range("T1:V1").DataSeries Rowcol:=xlRows, Type:=xlLinear, Step:=1 
        'enter the first value in W1 
        .Range("W1") = "1" 
        'use Fill Series to enter numbers in the rest of the range 
        .Range("W1:AE1").DataSeries Rowcol:=xlRows, Type:=xlLinear, Step:=1 
    End With 
Next ws 

With WS2 
    'copy row 1 on 2006 ws and paste to row 1 on Not Reserved ws 
    .Rows("1:1").Copy Destination:=wsNotReserved.Rows("1:1") 
End With 

' Sort and Fit Sheets 
'you are doing the same thing (other than the sort) for all 3 sheets--can use a loop 
For Each ws In Sheets(Array(WS1.Name, WS2.Name, wsNotReserved.Name)) 
    With ws 
        'only selecting the sheet/range so freeze panes can be activated 
        .Select 
        .Range("D2").Select 
        ActiveWindow.FreezePanes = True 
        With .Columns("H:H") 
            .NumberFormat = "[<=9999999]###-####;(###) ###-####" 
            .HorizontalAlignment = xlLeft 
            .VerticalAlignment = xlBottom 
            .WrapText = False 
            .MergeCells = False 
        End With 
        .Cells.EntireColumn.AutoFit 
        Select Case .Name 'check the name of the worksheet is being checked 
            Case Is = WS1.Name 'if the current sheet name is 2005 
                'perform this sort 
                .Cells.Sort Key1:=.Range("C2"), Order1:=xlAscending, Key2:=.Range("T2") _ 
                , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ 
                False, Orientation:=xlTopToBottom 
                .Range("C1").Select 
            Case Else 'if current sheet is 2006 or Not Reserved 
                'perform this sort 
                .Cells.Sort Key1:=.Range("C2"), Order1:=xlAscending, Key2:=.Range("AF2") _ 
                , Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _ 
                , Orientation:=xlTopToBottom 
        End Select 
    End With 
Next ws 

'again, you are doing the same thing (other than the sort) for multiple sheets--can use a loop 
For Each ws In Sheets(Array(WS2.Name, wsNotReserved.Name)) 
    With ws 
        .Range("A:B,G:G,I:I,K:L,N:N,P:Q,AG:AK,AM:AN,AQ:AT,AV:AW").EntireColumn.Hidden = True 
        .Columns("F:F").ColumnWidth = 3.33 
        .Columns("H:H").ColumnWidth = 14.44 
        .Columns("AF:AF").ColumnWidth = 4.89 
        .Columns("AX:AX").ColumnWidth = 80 
        .Cells.RowHeight = 26 
        .Range("AX1") = "Comment" 
        LastRow = .Cells(Rows.Count, "C").End(xlUp).Row 
        With .Range("$A$2:$AX" & LastRow) 
            'add border around the outside of the range in one go 
            .BorderAround 
            'add borders inside the range 
            With .Borders(xlInsideVertical) 
                .LineStyle = xlContinuous 
                .Weight = xlThin 
                .ColorIndex = xlAutomatic 
            End With 
            With .Borders(xlInsideHorizontal) 
                .LineStyle = xlContinuous 
                .Weight = xlThin 
                .ColorIndex = xlAutomatic 
            End With 
        End With 
        On Error Resume Next 
        .Cells.AutoFilter 
        On Error GoTo 0 
        
        .Rows("1:1").Insert Shift:=xlDown 
        .Range("C1").FormulaR1C1 = "=R[2]C[41]&"" Extracted ""&(TEXT(R[2]C[13],""mm/dd/yy""))" 
    
        ' Set up page and print area 
        LastRow = .Cells(Rows.Count, "C").End(xlUp).Row 
        With .PageSetup 
            .PrintArea = "$A$1:$AX" & LastRow 
            .PrintTitleRows = "$1:$2" 
            .PrintTitleColumns = "$C:$C" 
            .LeftHeader = "" 
            .CenterHeader = "" 
            .RightHeader = "" 
            .LeftFooter = "" 
            .CenterFooter = "" 
            .RightFooter = "" 
            .LeftMargin = Application.InchesToPoints(0.25) 
            .RightMargin = Application.InchesToPoints(0.25) 
            .TopMargin = Application.InchesToPoints(0.25) 
            .BottomMargin = Application.InchesToPoints(0.25) 
            .HeaderMargin = Application.InchesToPoints(0.25) 
            .FooterMargin = Application.InchesToPoints(0.25) 
            .PrintHeadings = False 
            .PrintGridlines = False 
            .PrintComments = xlPrintNoComments 
            '.PrintQuality = 600 'Taken out do to User Excel Version Conflicts 
            .CenterHorizontally = False 
            .CenterVertically = False 
            .Orientation = xlLandscape 
            .Draft = False 
            .PaperSize = xlPaperLegal 
            .FirstPageNumber = xlAutomatic 
            .Order = xlOverThenDown 
            .BlackAndWhite = False 
            .Zoom = False 
            .FitToPagesWide = 2 
            .FitToPagesTall = False 
        End With 
    
        'Count Days 
        LastRow = .Cells(Rows.Count, "C").End(xlUp).Row 
        .Range("T3").FormulaR1C1 = "=SUMPRODUCT(--(MONTH(ROW(INDIRECT(RC18&"":""&(RC19-1))))=R2C))" 
        .Range("T3").AutoFill Destination:=.Range("T3:AE3"), Type:=xlFillDefault 
        .Range("T3:AE3").AutoFill Destination:=.Range("T3:AE" & LastRow), Type:=xlFillDefault 
        .Range("T3:AE" & LastRow).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)" 
        Application.Goto .Range("C1") 
    End With 
Next ws 

Application.ScreenUpdating = True 
End Sub
```

Can you easily write some code to add the progress indicator to keep track of the macro as a whole and the sub process of comparing the names on the two sheets? 
I know this is probably quite the problem to ask for... But any help is greatly appreciated.


----------



## Greg Truby (May 1, 2006)

_And this is my response:_

Dear powerpackinduo,

It should go without saying, but this is *not* tested, so you may have to work out a couple of kinks.  Below is your code edited to impliment my multi-stage progress indicator.  (I did make a couple of edits to other bits of your code; but nothing major.)  Note that you'd need to edit your sheet's code module so that the button's click method simply calls *Main_UsingPI.*  All of the code below would be in a *standard* module.

A couple of comments:
Note that you really don't need to know much about the class module.  You only need to understand how to use the *.Add, UpdateProgressMinor,* and *UpdateProgressMajor* methods and the *.ProgramName* property.  That's it.  The rest of the module's properties affect aethetics, really.
My compliments on using Hungarian tags.  It makes it so much easier to scan through another programmer's code when this is done.
Again -- this might take a bit of tweaking, but it should get you started; and most of all it should show that you really don't need to know beans about all the code within the class module.  If you just walk through the implementation sample code, that's all you *need* to understand.  Understanding the rest is just for fun!


```
Public frmProg As ufxl_ProgressIndicator


Public Sub Main_UsingPI()

    Set frmProg = New ufxl_ProgressIndicator
    
    Load frmProg
    With frmProg
        .Caption = "Transfer Reservations"
        .Add "Copying", "copy"
        .Add "Second Process"
        .Add "Formatting", "format"
        .Add "Print Setup + Add Formulae", "last"
'        .ShowSubProcess = False
        .EstimateTimes = True
        .ProgramName = "Main"
        .ColorTotalBar = RGB(96, 0, 128)
        .Show
    End With
End Sub


Private Sub Main()
'      Compare 2005 and 2006 and transfers all people who have
'      not reserved in 2006 and put them on a separate sheet.

'// Since this name appears in a couple of places down in the code
'// easier maintenance if put up top as a constant. Then if ever want
'// to change, don't have to do a FIND/REPLACE thing -- just change
'// up top and all done.

Const c_strNotResdName As String = "Not Reserved"

Dim lRow As Long
Dim R As Range, rData As Range
Dim sCur As String
Dim WS1 As Worksheet, WS2 As Worksheet
Dim wsNotReserved As Worksheet, ws As Worksheet
Dim LastRow As Long, lngSrcRowCnt As Long
Dim p!

Application.ScreenUpdating = False

Set WS1 = Sheets("2005")
Set WS2 = Sheets("2006")
Set rData = WS2.Range("C2:C" & WS2.Cells(Rows.Count, "C").End(xlUp).Row)

' Check whether macro has already produced the
' Not Reserved sheet and delete it.

On Error Resume Next        'if error occurs, continue to next line of code

'attempt to set ws variable
Set wsNotReserved = Sheets(c_strNotResdName)

'if sheet already exists, no error will occur
If Err = 0 Then 'if sheet exists
    wsNotReserved.Cells.Clear
    
    '// ¿¿¿ only need to do this to WS2 if wsNotReserved existed??? //
    With WS2
        'only selecting sheet to remove freeze panes
        .Select
        ActiveWindow.FreezePanes = False
        .Cells.EntireColumn.Hidden = False
        .AutoFilterMode = False
        .Columns("T:AE").Delete
        .Rows("1:1").Delete
    End With
Else
    Set wsNotReserved = Sheets.Add(After:=Sheets(3))
    wsNotReserved.Name = c_strNotResdName
End If

On Error GoTo 0 'reset error trapping

'since the Not Reserved ws is being deleted and/or added as a new *blank* sheet
'every time, this statement will always result in lRow being set as 1
'You can just hard-code this value as 1 to save a tiny bit of time
lRow = 1
'lRow = wsNotReserved.Cells(Rows.Count, "C").End(xlUp).Row

'  **** SUBPROCCESS OF COMPARING SHEETS AND PLACING ON
' A NEW SHEET.  I WOULD LIKE TO TRACK THIS IN IT'S OWN
' PROGRESS BAR.
lngSrcRowCnt = WS1.Cells(Rows.Count, "C").End(xlUp).Row
For Each R In WS1.Range("C2:C" & lngSrcRowCnt)
    sCur = R.Text
    If Application.CountIf(rData, sCur) = 0 Then
        lRow = lRow + 1
        wsNotReserved.Rows(lRow).Value = WS1.Rows(R.Row).Value
    End If
    frmProg.UpdateProgressMajor R.Row / lngSrcRowCnt, "copy"
Next R
' **** END OF SUBPROCESS

'can loop so sheets/ranges do not have to be selected
p! = 0!
For Each ws In Sheets(Array(WS2.Name, wsNotReserved.Name))
    With ws
        .Columns("T:AE").Insert Shift:=xlToRight
        'enter the first value in T1
        .Range("T1") = "10"
        frmProg.UpdateProgressMinor 0.2
        'use Fill Series to enter numbers in the rest of the range
        .Range("T1:V1").DataSeries Rowcol:=xlRows, Type:=xlLinear, Step:=1
        frmProg.UpdateProgressMinor 0.6
        'enter the first value in W1
        .Range("W1") = "1"
        'use Fill Series to enter numbers in the rest of the range
        .Range("W1:AE1").DataSeries Rowcol:=xlRows, Type:=xlLinear, Step:=1
        frmProg.UpdateProgressMinor 1
    End With
    p! = p! + 0.5!
    frmProg.UpdateProgressMajor p!, 2
Next ws

With WS2
    'copy row 1 on 2006 ws and paste to row 1 on Not Reserved ws
    .Rows("1:1").Copy Destination:=wsNotReserved.Rows("1:1")
End With

' Sort and Fit Sheets
'you are doing the same thing (other than the sort) for all 3 sheets--can use a loop
p = 0!
For Each ws In Sheets(Array(WS1.Name, WS2.Name, wsNotReserved.Name))
    With ws
        'only selecting the sheet/range so freeze panes can be activated
        .Select
        .Range("D2").Select
        ActiveWindow.FreezePanes = True
        With .Columns("H:H")
            .NumberFormat = "[<=9999999]###-####;(###) ###-####"
            .HorizontalAlignment = xlLeft
            .VerticalAlignment = xlBottom
            .WrapText = False
            .MergeCells = False
        End With
        .Cells.EntireColumn.AutoFit
        Select Case .Name 'check the name of the worksheet is being checked
            Case Is = WS1.Name 'if the current sheet name is 2005
                'perform this sort
                .Cells.Sort Key1:=.Range("C2"), Order1:=xlAscending, Key2:=.Range("T2") _
                , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
                False, Orientation:=xlTopToBottom
                .Range("C1").Select
            Case Else 'if current sheet is 2006 or Not Reserved
                'perform this sort
                .Cells.Sort Key1:=.Range("C2"), Order1:=xlAscending, Key2:=.Range("AF2") _
                , Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
                , Orientation:=xlTopToBottom
        End Select
    End With
    p! = p! + 1!
    frmProg.UpdateProgressMajor p! / 3!, "format"
Next ws

'again, you are doing the same thing (other than the sort) for multiple sheets--can use a loop
p = 0
For Each ws In Sheets(Array(WS2.Name, wsNotReserved.Name))
    With ws
        .Range("A:B,G:G,I:I,K:L,N:N,P:Q,AG:AK,AM:AN,AQ:AT,AV:AW").EntireColumn.Hidden = True
        .Columns("F:F").ColumnWidth = 3.33
        .Columns("H:H").ColumnWidth = 14.44
        .Columns("AF:AF").ColumnWidth = 4.89
        .Columns("AX:AX").ColumnWidth = 80
        .Cells.RowHeight = 26
        .Range("AX1") = "Comment"
        frmProg.UpdateProgressMinor 0.15
        LastRow = .Cells(Rows.Count, "C").End(xlUp).Row
        With .Range("$A$2:$AX" & LastRow)
            'add border around the outside of the range in one go
            .BorderAround
            'add borders inside the range
            With .Borders(xlInsideVertical)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With .Borders(xlInsideHorizontal)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
        End With
        .AutoFilterMode = False
        frmProg.UpdateProgressMinor 0.3
        .Rows("1:1").Insert Shift:=xlDown
        .Range("C1").FormulaR1C1 = "=R[2]C[41]&"" Extracted ""&(TEXT(R[2]C[13],""mm/dd/yy""))"
    
        ' Set up page and print area
        LastRow = .Cells(Rows.Count, "C").End(xlUp).Row
        With .PageSetup
            .PrintArea = "$A$1:$AX" & LastRow
            .PrintTitleRows = "$1:$2"
            .PrintTitleColumns = "$C:$C"
            .LeftHeader = ""
            .CenterHeader = ""
            .RightHeader = ""
            .LeftFooter = ""
            .CenterFooter = ""
            .RightFooter = ""
            .LeftMargin = Application.InchesToPoints(0.25)
            .RightMargin = Application.InchesToPoints(0.25)
            .TopMargin = Application.InchesToPoints(0.25)
            .BottomMargin = Application.InchesToPoints(0.25)
            .HeaderMargin = Application.InchesToPoints(0.25)
            .FooterMargin = Application.InchesToPoints(0.25)
            .PrintHeadings = False
            .PrintGridlines = False
            .PrintComments = xlPrintNoComments
            '.PrintQuality = 600 'Taken out do to User Excel Version Conflicts
            .CenterHorizontally = False
            .CenterVertically = False
            .Orientation = xlLandscape
            .Draft = False
            .PaperSize = xlPaperLegal
            .FirstPageNumber = xlAutomatic
            .Order = xlOverThenDown
            .BlackAndWhite = False
            .Zoom = False
            .FitToPagesWide = 2
            .FitToPagesTall = False
        End With
    
        frmProg.UpdateProgressMinor 0.5
        'Count Days
        LastRow = .Cells(Rows.Count, "C").End(xlUp).Row
        .Range("T3").FormulaR1C1 = "=SUMPRODUCT(--(MONTH(ROW(INDIRECT(RC18&"":""&(RC19-1))))=R2C))"
        .Range("T3").AutoFill Destination:=.Range("T3:AE3"), Type:=xlFillDefault
        .Range("T3:AE3").AutoFill Destination:=.Range("T3:AE" & LastRow), Type:=xlFillDefault
        .Range("T3:AE" & LastRow).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)"
        Application.Goto .Range("C1")
    End With
    p = p + 0.5
    frmProg.UpdateProgressMajor p, "last"
Next ws

Application.ScreenUpdating = True
End Sub
```


----------



## Greg Truby (May 4, 2005)

Howdy folks, 

I'm in the middle of a rather large (months-long) project, so I haven’t spent much time on the boards lately.  One aspect of many projects is keeping the user informed of progress while processes are executing.  Quite a while back I had written my own version of Walkenbach’s userform-with-label as progress indicator.  Basically, I'd added more bars (labels if you want to be technical) to Walkenbach's form so that I could track progress on a half-dozen major processes as well as having a bar for sub-processes.  However, I found adapting my multi-bar form to use in other projects was cumbersome.  While trying to recycle it into my current project I got fed up enough with it to try using a UserForm as a class module (I remember reading somewhere that this is really what they are…) Anyhow, it seems to work pretty well as a class module.  And now I've got a progress-indicator tool that's pretty easy to drop into new projects.

So, I've cleaned it up reasonably well [500+ lines of code (thus far)] and would be willing to share it if'n it'd be of interest to anyone.  So anyone interested?


----------



## powerpackinduo (May 1, 2006)

Thanks for taking the time to do all of that for me.
I've started to try it and I was going to go line by line...
But I came across a problem at the first line when I stepped into it.

```
Public frmProg As ufxl_ProgressIndicator
```
Generates:
Compile Error:
User-defined type not defined

I was going to comment that out and keep going...  but that didn't work.
All I did was cut and paste your code as shown in your post and go from there...  Also I'm using Excel 2000.

Can you tell me what's wrong?


----------



## Greg Truby (May 1, 2006)

That would be because you need to have a copy of the userform in your project before you can declare a variable to be of that object type.  (The same holds true for ClassModules too.) Assuming you have the downloaded workbook of mine open in Excel, then the simplest way is to just use the _Project Explorer_ window in the VB Editor and drag the item from one project to another:

(Before)



_(click thumbnail image for larger view)_




_(click thumbnail image for larger view)_


----------



## Greg Truby (May 1, 2006)

One suggestion that I would give would be that if you are going to step through the code, excluding the call to _Show,_ and certainly when in *Main,* I would step through using _Shift+F8_ instead of just the _F8_ key otherwise you are going to end up diving in and out of the class module itself.  Once you have walked through using Shift+F8, you might consider going back and using just the F8 key to see _everything_ that's going on, but you might find it a tad perplexing at first, especially if you have not played with Class Modules very much.


----------



## powerpackinduo (May 1, 2006)

This is quite the crash course. LOL
I feel completely out of my element but I'm going to get this.

This time when I try to run it:

```
Public frmProg As ufxl_ProgressIndicator
```
Generates:
Compile Error:
Private object modules cannot be used in public object modules as parameters or return types for public procedures, as public data members, or as fields of public user defined types

I dragged the ProgressIndicator into the Project and cut and pasted the code...

I've tried changing 'public' to 'private' and vice/versa in both code window and no luck.

Any more help?
Thanks for all the time you're taking, I really appreciate it.


----------



## Greg Truby (May 1, 2006)

Did you see this bit in my original "how to" post?



> ...Note that you'd need to edit your sheet's code module so that the button's click method simply calls *Main_UsingPI.*  All of the code below would be in a *standard* module...



My guess is that you are trying to put this code into the code module for the worksheet.  Worksheet/workbook code modules are actually a species of *class* module and there are restrictions.  Among them: no public variables can be declared in a class module.  Is that our error?

<sup>edit</sup> If that's *not* our error, then I'll need to find someone with XL2000 to test this 'cause we may be running into a version issue. <sub>/edit</sub>


----------



## powerpackinduo (May 2, 2006)

Hey Greg,
You're correct I did not 'read' LOL  after I put it in the Standard Module it worked just fine.

I do have one problem with it that I haven't been able to figure out and I've been trying to walk through it with your xls example also where it works fine.
When the code is finished the progress indicator stays on the screen and the last sub process stay at 50% yet the Total Progress shows complete.
It stops at the last line of the following code and doesn't do anything:

```
Private Sub UserForm_Activate()
'....code truncated for space...
                                                m_varArguments(4), _
                                                m_varArguments(5)
        Case Else:
            MsgBox "You need to add code to the UserForm_Activate event handler" & _
                    vbCr & "so that it can pass " & Me.ArgumentCount _
                    & " arguments to " & Me.ProgramName & ".", _
                    vbCritical, "Programming Error"
            Unload Me
    End Select
End Sub
```
Yet on your example it keeps going to the UserForm_Terminate to clear out the progress indicator.  (Also displays the hello world message)
Is there some more code I need to add to the routine to have it continue?
The Hello world message is fine if that needs to pop up I can change that to something appropriate.
If you'd like me to send the code to you again I can, but it's the same as above.
Thanks for your help, this tool is awesome.


----------



## Greg Truby (May 2, 2006)

Glad to hear there are no version issues.

As for the other -- that would be because you are not telling VBA to fold up the form and put it back in the drawer...

In my sample code, the "Hello World" thing was just a way to demonstrate that parameters can be passed into the _MAIN()_ procedure and that passed-in parameters can be accessed after the form is dismissed from memory.  All you need to add is simply
	
	
	
	
	
	



```
Unload frmProg
```
after you no longer need the progress indicator to be displayed.  You can put this at the end of _MAIN()_ or you can place it after the closing *End With* in _Main_UsingPI()_


----------



## powerpackinduo (May 3, 2006)

That worked great.
I'm going to have so much fun playing and learning from this example.
Thanks for all of your help.  It's greatly appreciated!
I Love This Board!


----------

