Problems with a Progress Bar

[mctopher]

One of my processes is quite time consuming so I'm trying to add a progress bar so the user knows the macro is still running. The problem is when the code gets to the point where the progress bar user form is shown ("UserForm1.Show"), everything stops.

This is my first attempt at a progress bar, so I'm sure I'm missing something simple. Below is an excerpt from the code where the progress bar should kick in.

Any suggestions would be greatly appreciated.

CurrentRow = 2

UserForm1.LabelProgress.Width = 0
UserForm1.Show

While CurrentRow <= LastRow

    Combo = Left(Cells(CurrentRow, ShipToOrgNameColumn), 3) & "-" _
    & Cells(CurrentRow, ItemNumberColumn).Value & "-" & Len(Cells(CurrentRow, ItemNumberColumn))
    
    On Error Resume Next
        
    MatchedRowNumber = WorksheetFunction.Match(Combo, DCPSheet.Range("A:A"), 0)
        
    If MatchedRowNumber = 0 Then
            
        POData.Range("C" & CurrentRow) = ""
            
    Else
            
        POData.Range("C" & CurrentRow).Value = DCPSheet.Range("N" & MatchedRowNumber).Value
        
    End If
        
    CurrentRow = CurrentRow + 1
    MatchedRowNumber = 0

    PctDone = CurrentRow / LastRow

    With UserForm1
        .FrameProgress.Caption = Format(PctDone, "0%")
        .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
    End With
        
Wend

Using Excel 2010 and Windows 7

 

[Andrew Poulsom]

Your UserForm is modal so code execution is suspended until it is dismissed. You need to put your code in a procedure that's called by the UserForm_Activate event procedure. Example here:

http://spreadsheetpage.com/index.php/tip/displaying_a_progress_indicator/

 

[mctopher]

The issue I'll run into with that is many of the variables I use for this portion are used throughout other portions. Is there a way to pass variables into and out of the UserForm_Activate event procedure similar to passing them between other subroutines? Variables include primarily workbook and worksheet references needed to pull in data from other sources. If this is possible could you tell me the syntax for passing it?

 

[Andrew Poulsom]

If you declare your variables as Public in a General module (and nowhere else) they will be visible to all modules in the workbook.

 

[mctopher]

I've never set global variables before, so my apologies for the further questions.

Checking through other references it sounds like I should be able to set global variables by using the work "Public" at the start of the sub, or "Public" instead of "Dim" for the variable. Neither of these seem to work. "Public Sub..." doesn't seem to have an impact on the variables being pulled after the code returns to the last sub that should house the status bar. "
Public Template As Workbook" kicks back an error message of "Invalid attribute in Sub or Function.

Could you point me the right direction to set public variables? Below is the full coding I have so far if that's helpful to point out my error.


General module:

Public Sub PrioritizeOpenPO()

Dim Template As Workbook
Dim DCPotential As Workbook

Dim Controls As Worksheet
Dim DCPSheet As Worksheet
Dim POData As Worksheet

Dim DaysPastPromiseColumn As Integer
Dim ShipToOrgNameColumn As Integer
Dim ItemNumberColumn As Integer

Dim LastRow As Long
Dim CurrentRow As Long
Dim DCPLastRow As Long

Dim PctDone As Single

Set Template = ActiveWorkbook
Set Controls = Sheets("Controls")
Set POData = Sheets("Open PO Data")

LastRow = POData.Range("A1048576").End(xlUp).Row

MsgBox "Please select the most recent DC Potential file."

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose the DC Potential file to import", _
filefilter:="Excel Files *.xl* (*.xl*),")

If FileToOpen = False Then
    
    MsgBox "No file specified, Cancelling process."

    Exit Sub

End If
   
POData.Range("A1").Resize(, 3).EntireColumn.Insert
POData.Range("A1").Value = "Late"
POData.Range("B1").Value = "Type"
POData.Range("C1").Value = "Potential"

DaysPastPromiseColumn = WorksheetFunction.Match("Days Past Promise", POData.Range("1:1"), 0)
ShipToOrgNameColumn = WorksheetFunction.Match("Ship_To_Organization_Name", POData.Range("1:1"), 0)
ItemNumberColumn = WorksheetFunction.Match("Item No", POData.Range("1:1"), 0)

CurrentRow = 2
    
While CurrentRow <= LastRow
    
    If POData.Cells(CurrentRow, DaysPastPromiseColumn) > 0 Then
        POData.Range("A" & CurrentRow).Value = "Late"
    Else
        POData.Range("A" & CurrentRow).Value = "Not Late"
    End If
    
    CurrentRow = CurrentRow + 1

Wend

Workbooks.Open FileName:=FileToOpen

Set DCPotential = ActiveWorkbook
Set DCPSheet = DCPotential.Sheets(1)

DCPLastRow = DCPSheet.Range("B1048576").End(xlUp).Row

CurrentRow = 2

UserForm1.LabelProgress.Width = 0
UserForm1.Show

End Sub

Code associated with UserForm1

Private Sub UserForm_activate()
    Call UseDCP
End Sub

Final sub in General module (where the progress par should be displayed

Sub UseDCP()

While CurrentRow <= LastRow

    Combo = POData.Left(Cells(CurrentRow, ShipToOrgNameColumn), 3) & "-" _
    & POData.Cells(CurrentRow, ItemNumberColumn).Value & "-" & POData.Len(Cells(CurrentRow, ItemNumberColumn))
    
    On Error Resume Next
        
    MatchedRowNumber = WorksheetFunction.Match(Combo, DCPSheet.Range("A:A"), 0)
        
    If MatchedRowNumber = 0 Then
            
        POData.Range("C" & CurrentRow) = ""
            
    Else
            
        POData.Range("C" & CurrentRow).Value = DCPSheet.Range("N" & MatchedRowNumber).Value
        
    End If
        
    CurrentRow = CurrentRow + 1
    MatchedRowNumber = 0

    PctDone = CurrentRow / LastRow

    With UserForm1
        .FrameProgress.Caption = Format(PctDone, "0%")
        .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
    End With
        
Wend
    
'Sort by Org, Item, Date (Newest to Oldest)

Template.Activate
Controls.Select
Range("A1").Select

Application.CutCopyMode = False
DCP.Close

MsgBox "Initial Priorities Completed"

End Sub

Again, thank you for your time and help with this.

 

[Andrew Poulsom]

You have to declare your variables as Public at the top of the module, before any procedures. Variables declared within a procedure are only visible to that procedure. Here are some more details:

http://www.cpearson.com/Excel/Scope.aspx

 

[mctopher]

Gotcha, I didn't realize that you meant before anything at all, including "Sub". Doing the variables as public prior to starting the sub seems to be working. Thanks for the info and the reference!