Add a SIMPLE progress indicator/bar to an existing Macro?


Board Regular
Apr 30, 2019
Hi guys

I've been trying to research how I could add a progress bar to an existing macro of mine but I can't seem to find a reliable tutorial (probably just my poor understanding!). I was wondering if anyone would be able to link me to or help me out with a simple progress indicator or progress bar that I could apply to my following Macro code? :biggrin: I would be EXTREMELY grateful! The macro isn't particular onerous but can take a while sometimes, so I thought a nice progress bar/indicator might look fancy! I would also like to understand the logic behind this so I can apply it to other macros that I use that are more comprehensive. Thank you so much for reading.

Sub BOMTest()

' Get customer workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook

Dim aCount As Integer, msg As String
Const msg1 = "The BOM has been imported!" & vbCr & vbCr
Const msg2 = " item(s) could not be found." & vbCr & vbCr & "Please update the 'Equipment Cost Lookup' sheet to add the pricing for the missing product code(s) and then try again."
Const msg3 = "All items have been successfully imported."

' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook

' get the customer workbook
filter = "Text files (*.xlsx),*.xlsx"
caption = "Please select the BOM "
customerFilename = Application.GetOpenFilename(filter, , caption)

Set customerWorkbook = Application.Workbooks.Open(customerFilename)

' assume range is A1 - C10 in sheet1
' copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(1)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)

targetSheet.Range("F14", "I48").Value = sourceSheet.Range("A2", "K48").Value

' Close customer workbook

targetSheet.Range("F7").Value = customerFilename

aCount = WorksheetFunction.CountIfs(Sheets("Dashboard").Range("J14:J64"), "Item Not Found", Sheets("Dashboard").Range("I14:I64"), ">0")
If aCount = 0 Then msg = msg1 & msg3 Else msg = msg1 & aCount & msg2
MsgBox msg, vbInformation
End Sub

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.


Board Regular
Feb 5, 2015
Pulling this from an old project, so hopefully it works out for you!
I think I found this code somewhere, so sorry I don't remember where to give attribution to.

On a UserForm, I have a label called labelExportProgress . Back Color is red and it's inside a square box called exportProgress to give the 'outline' of the progress bar, along with the % on top.
The code to 'increase' the bar is
Sub UpdateProgressBar(PctDone As Single)
    With UserForm1

        'Update the Caption property of the Frame control.
        .exportProgress.Caption = Format(PctDone, "0%")

        'Widen the Label control.
        .labelExportProgress.Width = PctDone * _
            (.exportProgress.Width - 10)
    End With

    'The DoEvents allows the UserForm to update.
End Sub
Within the other code I know how many I'm looping through total, and keep track of how many I've done, then update the progress bar
'Update the percentage completed.
            percentProgress = counter / numberOfFilesInFolder
            counter = counter + 1
            'Call subroutine that updates the progress bar.
            UpdateProgressBar percentProgress
After the code ends, sometimes the progress bar is a little wonky (slightly not 100%, etc), so I just set to 100% after everything completes
Application.ScreenUpdating = True
UpdateProgressBar (1) 'Set to 100% complete
Last edited:

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...