How to call a progress bar procedure correctly while looping through rows in Excel

So this is the code I have for one module on my excel form. In it's current state it works great.  

Private Sub CMD_RUN_IOC_CNE_Click()

Set ws = ThisWorkbook.Sheets("CNE")
ws1 = ThisWorkbook.Sheets("CNE").Name

LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

xMsg1 = MsgBox("Are you sure you want to Continue, this could take longer than 10 minutes??", vbYesNo, "Proceed with Export?")
        If xMsg1 = vbYes Then
            GoTo MoveOn
        Else
            GoTo Graceful_Cancel
        End If
MoveOn:

For i = 2 To LastRow
If ws.Range("H" & i).value = "Closed" Then GoTo Skip

If MyStartingNumber = 0 Then
MyStartingNumber = Me.Starting_Number
Else
MyStartingNumber = Me.Starting_Number + 1
End If

Me.Starting_Number = MyStartingNumber
UI = Me.UserInitials

Set objWord = CreateObject("Word.Application")
'objWord.Visible = True

Application.StatusBar = "Updating Sample_IOC.docx with selected data and saving as IOC Number.  Please wait...."

objWord.Documents.Open IOCS

With objWord.ActiveDocument

.Bookmarks("Title_CO").Range.Text = ws.Range("D" & i).value                                                                                       'Bookmark 1
.Bookmarks("IOC_NO").Range.Text = "IOC-" & Me.UserInitials.value & "-" & Format(Me.Start_Date.value, "YY") & "-" & Me.Starting_Number.value       'Bookmark 2
.Bookmarks("Start_Date").Range.Text = Me.Start_Date.value                                                                                         'Bookmark 3
.Bookmarks("End_Date").Range.Text = Me.Start_Date.value                                                                                           'Bookmark 4
.Bookmarks("Subject").Range.Text = ws.Range("D" & i).value                                                                                        'Bookmark 5
.Bookmarks("Date").Range.Text = Me.Start_Date.value                                                                                               'Bookmark 6
.Bookmarks("From").Range.Text = ws.Range("I" & i).value                                                                                           'Bookmark 7
.Bookmarks("CO_Num").Range.Text = ws.Range("D" & i).value                                                                                         'Bookmark 8
.Bookmarks("CO_Title").Range.Text = ws.Range("F" & i).value                                                                                       'Bookmark 9
.Bookmarks("Verification_Method").Range.Text = ws.Range("H" & i).value                                                                            'Bookmark 10
.Bookmarks("CO_Objective").Range.Text = ws.Range("D" & i).value                                                                                   'Bookmark 11
.Bookmarks("SR_Title").Range.Text = ws1 & "_HRS"                                                                                                  'Bookmark 12
.Bookmarks("Doors_ID").Range.Text = ws.Range("C" & i).value                                                                                       'Bookmark 13
.Bookmarks("Source_Objective").Range.Text = ws.Range("G" & i).value                                                                               'Bookmark 14
.Bookmarks("SC_1").Range.Text = ws.Range("J" & i).value                                                                                           'Bookmark 15
.Bookmarks("SC_2").Range.Text = ws.Range("K" & i).value                                                                                           'Bookmark 16
.Bookmarks("SC_3").Range.Text = ws.Range("L" & i).value                                                                                           'Bookmark 17
.Bookmarks("SC_1_DR_1").Range.Text = ws.Range("P" & i).value                                                                                      'Bookmark 18
.Bookmarks("SC_2_DR_2").Range.Text = ws.Range("Q" & i).value                                                                                      'Bookmark 19
'.Bookmarks("SC_3_DR_3").Range.Text = ws.Range("R" & i).Value                                                                                     'Bookmark 20; Not currently Used
.Bookmarks("U_Class1").Range.Text = ws.Range("M" & i).value                                                                                       'Bookmark 21
.Bookmarks("U_Class2").Range.Text = ws.Range("N" & i).value                                                                                       'Bookmark 22
.Bookmarks("U_Class3").Range.Text = ws.Range("O" & i).value                                                                                       'Bookmark 23
.Bookmarks("CO_NO").Range.Text = ws.Range("D" & i).value                                                                                          'Bookmark 24
.Bookmarks("Creation_Date").Range.Text = IOC_Form.Start_Date.value                                                                                'Bookmark 25
.Bookmarks("Footer").Range.Text = ws.Range("D" & i).value                                                                                         'Bookmark 26
.Bookmarks("Footer_1").Range.Text = ws.Range("D" & i).value                                                                                       'Bookmark 27
.Bookmarks("HDR_IOC").Range.Text = "IOC-" & Me.UserInitials.value & "-" & Format(Me.Start_Date.value, "YY") & "-" & Me.Starting_Number.value      'Bookmark 28

strPath = Me.IOC_CNE_Path & "\" & MyStartingNumber & ".docx"


objWord.ActiveDocument.SaveAs FileName:=strPath, _
AddToRecentFiles:=False
objWord.Quit False

End With

Skip:
Next i

gracefulExit:
    Application.StatusBar = False
    Set objWord = Nothing
    xMsg2 = MsgBox("Export Completed Successfully", vbOKOnly, "SUCCESS!!!")
    'objWord.Quit True
Exit Sub

Graceful_Cancel:
    Application.StatusBar = False
    Set objWord = Nothing
    xMsg3 = MsgBox("Export Canceled", vbOKOnly, "CANCELED!!!")
    'objWord.Quit True
    
End Sub

Open in new window



You will notice that one of the lines read:
Application.StatusBar = "Updating Sample_IOC.docx with selected data and saving as IOC Number.  Please wait...."

That's all the user gets as far as information goes about how the export is going

I would like to incorporate this progress bar into it.  I have imported the forms into the spreadsheet I have but I am unsure how to call it.
http://www.experts-exchange.com/articles/1756/A-VBA-Progress-Bar-for-Excel-and-Other-Microsoft-Apps.html


Any thoughts?

John
John SheehySystem Security ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Take a look at this project of mine. In the 'Procedure' module you'll find a sub called performActions. Currently that sub sets a MAX value of 5 and does 5 long actions.
Q-28673053.xls
John SheehySystem Security ManagerAuthor Commented:
I like how you did yours, but I can't really apply it to my code.

But I am having issues with incorporating any progress bar

As you can see in the snippet there I have two for statements  
For r is the increment for the status bar
For i is the increment for how many rows it needs to run through and export out to word

r and i will always = Lastrow  

r knows it has to update the status bar 131 times in this case  (So each fill of the status par is .76%)
and i knows it has to export out 131 records.

When it hits the Next I statement is when it does just that and r is totally skipped so the progress bar doesn't even update.

I have no idea how to use two for statements.  And even if I kept the progress bar as a separate function I would still not know how to tell it to update the progress  bar after it completed one export.

Make sense?

Private Sub CMD_RUN_IOC_CNE_Click()

IOC_Form.Hide

'        If diag.cancelIsPressed Then Exit For
 
    

Set ws = ThisWorkbook.Sheets("CNE")
ws1 = ThisWorkbook.Sheets("CNE").Name

LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

xMsg1 = MsgBox("Are you sure you want to Continue, this could take longer than 10 minutes??", vbYesNo, "Proceed with Export?")
        If xMsg1 = vbYes Then
            GoTo MoveOn
        Else
            GoTo Graceful_Cancel
        End If
MoveOn:
diag.Configure "Progress Meter", "Now Exporting each row...", 1, LastRow
diag.Show

For r = 2 To LastRow
    
        diag.SetValue r
        diag.SetStatus "Now Exporting each row..." & r
For i = 2 To LastRow
If ws.Range("H" & i).value = "Closed" Then GoTo Skip

If MyStartingNumber = 0 Then
MyStartingNumber = Me.Starting_Number
Else
MyStartingNumber = Me.Starting_Number + 1
End If

Me.Starting_Number = MyStartingNumber

Set objWord = CreateObject("Word.Application")

Application.StatusBar = "Updating Sample_IOC.docx with selected data and saving as IOC Number.  Please wait...."

objWord.Documents.Open IOCS

With objWord.ActiveDocument

.Bookmarks("Title_CO").Range.Text = ws.Range("D" & i).value                                                                                       'Bookmark 1
.Bookmarks("IOC_NO").Range.Text = "IOC-" & Me.UserInitials.value & "-" & Format(Me.Start_Date.value, "YY") & "-" & Me.Starting_Number.value       'Bookmark 2
.Bookmarks("Start_Date").Range.Text = Me.Start_Date.value                                                                                         'Bookmark 3
.Bookmarks("End_Date").Range.Text = Me.Start_Date.value                                                                                           'Bookmark 4
.Bookmarks("Subject").Range.Text = ws.Range("D" & i).value                                                                                        'Bookmark 5
.Bookmarks("Date").Range.Text = Me.Start_Date.value                                                                                               'Bookmark 6
.Bookmarks("From").Range.Text = ws.Range("I" & i).value                                                                                           'Bookmark 7
.Bookmarks("CO_Num").Range.Text = ws.Range("D" & i).value                                                                                         'Bookmark 8
.Bookmarks("CO_Title").Range.Text = ws.Range("F" & i).value                                                                                       'Bookmark 9
.Bookmarks("Verification_Method").Range.Text = ws.Range("H" & i).value                                                                            'Bookmark 10
.Bookmarks("CO_Objective").Range.Text = ws.Range("D" & i).value                                                                                   'Bookmark 11
.Bookmarks("SR_Title").Range.Text = ws1 & "_HRS"                                                                                                  'Bookmark 12
.Bookmarks("Doors_ID").Range.Text = ws.Range("C" & i).value                                                                                       'Bookmark 13
.Bookmarks("Source_Objective").Range.Text = ws.Range("G" & i).value                                                                               'Bookmark 14
.Bookmarks("SC_1").Range.Text = ws.Range("J" & i).value                                                                                           'Bookmark 15
.Bookmarks("SC_2").Range.Text = ws.Range("K" & i).value                                                                                           'Bookmark 16
.Bookmarks("SC_3").Range.Text = ws.Range("L" & i).value                                                                                           'Bookmark 17
.Bookmarks("SC_1_DR_1").Range.Text = ws.Range("P" & i).value                                                                                      'Bookmark 18
.Bookmarks("SC_2_DR_2").Range.Text = ws.Range("Q" & i).value                                                                                      'Bookmark 19
'.Bookmarks("SC_3_DR_3").Range.Text = ws.Range("R" & i).Value                                                                                     'Bookmark 20; Not currently Used
.Bookmarks("U_Class1").Range.Text = ws.Range("M" & i).value                                                                                       'Bookmark 21
.Bookmarks("U_Class2").Range.Text = ws.Range("N" & i).value                                                                                       'Bookmark 22
.Bookmarks("U_Class3").Range.Text = ws.Range("O" & i).value                                                                                       'Bookmark 23
.Bookmarks("CO_NO").Range.Text = ws.Range("D" & i).value                                                                                          'Bookmark 24
.Bookmarks("Creation_Date").Range.Text = IOC_Form.Start_Date.value                                                                                'Bookmark 25
.Bookmarks("Footer").Range.Text = ws.Range("D" & i).value                                                                                         'Bookmark 26
.Bookmarks("Footer_1").Range.Text = ws.Range("D" & i).value                                                                                       'Bookmark 27
.Bookmarks("HDR_IOC").Range.Text = "IOC-" & Me.UserInitials.value & "-" & Format(Me.Start_Date.value, "YY") & "-" & Me.Starting_Number.value      'Bookmark 28

strPath = Me.IOC_CNE_Path & "\" & MyStartingNumber & ".docx"

objWord.ActiveDocument.SaveAs FileName:=strPath, _
AddToRecentFiles:=False
objWord.Quit False

End With

Skip:
Next i
Next r


diag.Hide

gracefulExit:
    Application.StatusBar = False
    Set objWord = Nothing
    xMsg2 = MsgBox("Export Completed Successfully", vbOKOnly, "SUCCESS!!!")
Exit Sub

Graceful_Cancel:
    Application.StatusBar = False
    Set objWord = Nothing
    xMsg3 = MsgBox("Export Canceled", vbOKOnly, "CANCELED!!!")
    
End Sub

Open in new window

Martin LissOlder than dirtCommented:
Why do you need two For/Next loops? I would think that you could do away with the 'r' loop and inside the 'i' loop do diag.SetValue i.

Some other things:

1. Why are you creating/quitting the Word object 131 times? Couldn't you do that outside of the loop and save a lot of time?

2. As an old (in both senses of the word) VB6 programmer take it from me that outside of GoTo an error routine that you should never use GoTo when there's other ways to do what you want. Case in point is your If ws.Range("H" & i).Value = "Closed" Then GoTo Skip line. That could be easily changed to If ws.Range("H" & i).Value <> "Closed" Then and replace your Skip: line with an End If.

3. Finally I found the code hard to follow until I changed the indentation to look like this.
Private Sub CMD_RUN_IOC_CNE_Click()

IOC_Form.Hide

'        If diag.cancelIsPressed Then Exit For
 
    

    Set ws = ThisWorkbook.Sheets("CNE")
    ws1 = ThisWorkbook.Sheets("CNE").Name
    
    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    
    xMsg1 = MsgBox("Are you sure you want to Continue, this could take longer than 10 minutes??", vbYesNo, "Proceed with Export?")
    If xMsg1 = vbYes Then
        GoTo MoveOn
    Else
        GoTo Graceful_Cancel
    End If
MoveOn:
    diag.Configure "Progress Meter", "Now Exporting each row...", 1, LastRow
    diag.Show
    
    For r = 2 To LastRow
        
        diag.SetValue r
        diag.SetStatus "Now Exporting each row..." & r
        For i = 2 To LastRow
            If ws.Range("H" & i).Value = "Closed" Then GoTo Skip
        
            If MyStartingNumber = 0 Then
                MyStartingNumber = Me.Starting_Number
            Else
                MyStartingNumber = Me.Starting_Number + 1
            End If
        
            Me.Starting_Number = MyStartingNumber
            
            Set objWord = CreateObject("Word.Application")
            
            Application.StatusBar = "Updating Sample_IOC.docx with selected data and saving as IOC Number.  Please wait...."
            
            objWord.Documents.Open IOCS
            
            With objWord.ActiveDocument

                .Bookmarks("Title_CO").Range.Text = ws.Range("D" & i).Value                                                                                       'Bookmark 1
                .Bookmarks("IOC_NO").Range.Text = "IOC-" & Me.UserInitials.Value & "-" & Format(Me.Start_Date.Value, "YY") & "-" & Me.Starting_Number.Value       'Bookmark 2
                .Bookmarks("Start_Date").Range.Text = Me.Start_Date.Value                                                                                         'Bookmark 3
                .Bookmarks("End_Date").Range.Text = Me.Start_Date.Value                                                                                           'Bookmark 4
                .Bookmarks("Subject").Range.Text = ws.Range("D" & i).Value                                                                                        'Bookmark 5
                .Bookmarks("Date").Range.Text = Me.Start_Date.Value                                                                                               'Bookmark 6
                .Bookmarks("From").Range.Text = ws.Range("I" & i).Value                                                                                           'Bookmark 7
                .Bookmarks("CO_Num").Range.Text = ws.Range("D" & i).Value                                                                                         'Bookmark 8
                .Bookmarks("CO_Title").Range.Text = ws.Range("F" & i).Value                                                                                       'Bookmark 9
                .Bookmarks("Verification_Method").Range.Text = ws.Range("H" & i).Value                                                                            'Bookmark 10
                .Bookmarks("CO_Objective").Range.Text = ws.Range("D" & i).Value                                                                                   'Bookmark 11
                .Bookmarks("SR_Title").Range.Text = ws1 & "_HRS"                                                                                                  'Bookmark 12
                .Bookmarks("Doors_ID").Range.Text = ws.Range("C" & i).Value                                                                                       'Bookmark 13
                .Bookmarks("Source_Objective").Range.Text = ws.Range("G" & i).Value                                                                               'Bookmark 14
                .Bookmarks("SC_1").Range.Text = ws.Range("J" & i).Value                                                                                           'Bookmark 15
                .Bookmarks("SC_2").Range.Text = ws.Range("K" & i).Value                                                                                           'Bookmark 16
                .Bookmarks("SC_3").Range.Text = ws.Range("L" & i).Value                                                                                           'Bookmark 17
                .Bookmarks("SC_1_DR_1").Range.Text = ws.Range("P" & i).Value                                                                                      'Bookmark 18
                .Bookmarks("SC_2_DR_2").Range.Text = ws.Range("Q" & i).Value                                                                                      'Bookmark 19
                '.Bookmarks("SC_3_DR_3").Range.Text = ws.Range("R" & i).Value                                                                                     'Bookmark 20; Not currently Used
                .Bookmarks("U_Class1").Range.Text = ws.Range("M" & i).Value                                                                                       'Bookmark 21
                .Bookmarks("U_Class2").Range.Text = ws.Range("N" & i).Value                                                                                       'Bookmark 22
                .Bookmarks("U_Class3").Range.Text = ws.Range("O" & i).Value                                                                                       'Bookmark 23
                .Bookmarks("CO_NO").Range.Text = ws.Range("D" & i).Value                                                                                          'Bookmark 24
                .Bookmarks("Creation_Date").Range.Text = IOC_Form.Start_Date.Value                                                                                'Bookmark 25
                .Bookmarks("Footer").Range.Text = ws.Range("D" & i).Value                                                                                         'Bookmark 26
                .Bookmarks("Footer_1").Range.Text = ws.Range("D" & i).Value                                                                                       'Bookmark 27
                .Bookmarks("HDR_IOC").Range.Text = "IOC-" & Me.UserInitials.Value & "-" & Format(Me.Start_Date.Value, "YY") & "-" & Me.Starting_Number.Value      'Bookmark 28
            
                strPath = Me.IOC_CNE_Path & "\" & MyStartingNumber & ".docx"
                
                objWord.ActiveDocument.SaveAs Filename:=strPath, _
                AddToRecentFiles:=False
                objWord.Quit False

            End With

Skip:
        Next i
    Next r


    diag.Hide
    
gracefulExit:
        Application.StatusBar = False
        Set objWord = Nothing
        xMsg2 = MsgBox("Export Completed Successfully", vbOKOnly, "SUCCESS!!!")
    Exit Sub

Graceful_Cancel:
    Application.StatusBar = False
    Set objWord = Nothing
    xMsg3 = MsgBox("Export Canceled", vbOKOnly, "CANCELED!!!")
    
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

John SheehySystem Security ManagerAuthor Commented:
Sorry it has taken so long for me to get back.

I did as you suggested and did away with the 'r' loop.  That fixed the issue I was having and the bar updates as required.  Great suggestion thank you.

John
John SheehySystem Security ManagerAuthor Commented:
I did make the other changes you suggested as well.

John
Martin LissOlder than dirtCommented:
So I assume you'll be closing this question.
John SheehySystem Security ManagerAuthor Commented:
Solution was easy to understand and I was able to implement it right away.
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.