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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.


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

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...