VBA Progress Bar not Working

[wilt823]

The macro I created is done and loops through each customer in a range successfully; "rngCust" is one column of cells with a different customer name in each cell "cust". This macro can take a long time to run so I am trying to insert a progress bar to display the percentage complete. I got several online examples to work when I copied them using their code, but I have not yet been able to incorporate it into my macro successfully, even with lots of research on forums and websites. It seems like I'm doing it right, but...I'm still new to VBA and I feel like I must be missing a key concept here.

The idea is for the progress bar to show the percent complete as it goes from each "cust" cell in "rngCust" to the next cell, basically each iteration of the loop should update it.

This is the last set of changes I have tried, and here I get the error msg 1004-"Method Range of Object Global Failed" on the lastrow= in the variable definition; I also get error 91-"object variable or with block variable not set" on the pctCompl= line with the progress calculation in the body of the macro.

Here's what I have so far:

The user form macro is:
Private Sub UserForm_Activate()
Call CreateCustomerInvoices
End Sub

The main macro CreateCustomerInvoices is:
Dim wb As Workbook
Dim ws As Worksheet
Dim rngBilling As Range
Dim erps As Worksheet
Dim rngERPsumm As Range
Dim rngCust As Range
Dim cust As Range
Dim pctCompl As Single 'this is the first line of new code for the user form, 'everything above this is from the completed macro which runs 'correctly
Dim lastrow As Long 'this is also for the user form
lastrow = Range("rngCust" & Rows.Count).End(xlUp).Row ' '=rngCust.End(xlUp).Row does not get an error here
'but I still get the one below

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Billing")
Set rngBilling = ws.Range("rngBilling")
Set erps = wb.Worksheets("ERP Summary")
Set rngERPsumm = erps.Range("rngERPsumm")
Set rngCust = ws.Range("rngCust")

UserForm1.Show 'show user form- this works, the user form comes up

'LOOP for each cust in rngCust:

For Each cust In rngCust.Columns(1).Cells 'to look at a cell in each row of 'the first column of range

'this is the user form part:
pctCompl = cust / lastrow 'measure of progress, also tried '=cust/rngCust with no luck
With UserForm1
UserForm1.Text.Caption = pctCompl & "% Completed"
UserForm1.Bar.Width = pctCompl * 2
End With
DoEvents

'then it performs the rest of the macro here
'If statement
'IfElse
"IfElse
'Else
End If

Next cust

Unload UserForm1
End Sub

Any insight would be greatly appreciated.
Thank you.
Win10 Off 2013

 

[bobsan42]

There are several ideas here: Progress bar in VBA Excel
I personally prefer this one: Progress bar in VBA Excel

 

[wilt823]

The preferred one is one of several that I used to model my code, I even tried re-doing my code to exactly match that example and I still get error messages. I think the problem is that I don't know how to customize the code to make it work in my macro. Is that something you can help me with?

 

[bobsan42]

The one by Dharman is to show progress in the Statusbar (bottom left) - not on a form.
There is no real need to customize it - just use it like this:

showStatus Current, Total, "  Process Running: "

where Current is the number of the current operation, Total is the total number of operations, and then you can put any message for clarity

One thing if you use this one - consider changing the sub from Private to Public.
For me the advantage of this one is that it actually is faster than the progress bar in a form.

 

[wilt823]

I tried the status bar referenced above, with the bar at the bottom of the screen, and I still can't get past the same point in my macro:

Dim rngCust As Range
Set rngCust = ws.Range("rngCust")

Dim CurrentStatus As Integer
Dim NumberOfBars As Integer
Dim pctDone As Integer
Dim lastrow As Long, i As Long
lastrow = Range("rngCust" & Rows.Count).End(xlUp).Row

It's the same line I was using in the user form and I get the same error: 1004-"Method Range of Object Global Failed". The only change I made was to insert the name of my range in the first part of the line, isn't that what I am supposed to do? If so, then I am at a complete loss as to how to fix this, especially because it exactly matches all of the examples I've seen. Please help.

 

[DanteAmor]

Try this.
1. Create a control label named "lblRemain". With these characteristics:
Height: 48
Width: 360
Top: 22
Left: 11

2. Create another label named "lblDone" with these characteristics:
Height: 36
Width: 12
Top: 27
Left: 12
Backcolor: The one you want

Use this code:

Private Sub UserForm_Activate()
  Call CreateCustomerInvoices
End Sub

Sub CreateCustomerInvoices()
  Dim wb As Workbook, ws As Worksheet, erps As Worksheet
  Dim rngBilling As Range, rngERPsumm As Range, rngCust As Range, cust As Range
  Dim i As Long, lastrow As Long 
 
  Set wb = ThisWorkbook
  Set ws = wb.Worksheets("Billing")
  Set erps = wb.Worksheets("ERP Summary")
  Set rngBilling = ws.Range("rngBilling")
  Set rngERPsumm = erps.Range("rngERPsumm")
  Set rngCust = ws.Range("rngCust")
 
  lastrow = rngCust.Columns(1).Rows.Count
  For i = 1 To lastrow
    If i Mod 5 = 0 Then
      lblDone.Width = (i / lastrow) * (lblRemain.Width - 2)
      DoEvents
    End If
    'then it performs the rest of the macro here
  Next i
End Sub

How many records do you have in your range?
How long does it take to execute your code?
You can put your code here, maybe we can improve the code and the time.

 

[bobsan42]

lastrow = Range("rngCust" & Rows.Count).End(xlUp).Row

It's the same line I was using in the user form and I get the same error: 1004-"Method Range of Object Global Failed". The only change I made was to insert the name of my range in the first part of the line, isn't that what I am supposed to do? If so, then I am at a complete loss as to how to fix this, especially because it exactly matches all of the examples I've seen. Please help.

You will need to provide a worksheet object to clarify the Range parent. That's what the error message says - the program thinks Range is a method of the object Global, while Range is actually an Object, child of the Worksheet object.
You will need something like this:

lastrow = Worksheets("name of the sheet").Range("rngCust" & Rows.Count).End(xlUp).Row

Probably in your case Worksheets("name of the sheet") can be WS but I don't know the rest of your code.

 

[bobsan42]

Sorry but I didn't look carefully at your code.
some more remarks:

The other thing is rngCust is a range object - you cannot use it as string in quotes. Basically you need the last cell row number - you can use any column letter for this.
You will need something like this:

lastrow = ws.Range(Split(rngCust.Address(True, False), "$")(0) & ws.Rows.Count).End(xlUp).Row

However this line must be executed after you set the WS object and rngCust.