VBA Progress Bar not Working

wilt823

New Member
Joined
Feb 29, 2020
Messages
18
Office Version
  1. 2013
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

wilt823

New Member
Joined
Feb 29, 2020
Messages
18
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Jul 14, 2010
Messages
1,364
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:
VBA Code:
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

New Member
Joined
Feb 29, 2020
Messages
18
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
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:
VBA 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

Well-known Member
Joined
Jul 14, 2010
Messages
1,364
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:
VBA Code:
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

Well-known Member
Joined
Jul 14, 2010
Messages
1,364
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:
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,753
Messages
5,626,665
Members
416,199
Latest member
Gautamsunil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top