TarunVBA2026PB20

Question

TarunVBA2026PB20

Tarun Kumar 0

frmProgress

Option Explicit

Private Sub UserForm_Initialize()

'----------------------------------------

' FORM INITIAL SETTINGS

'----------------------------------------

Me.Caption = "Processing... 0%"

'White background / full bar

Label1.BackColor = vbWhite

'Green progress bar

Label2.BackColor = RGB(0, 176, 80)

'Start with empty progress

Label2.Width = 0

Label2.Caption = ""

End Sub

Private Sub UserForm_Activate()

'Make sure the progress bar starts empty

Label2.Width = 0

Me.Caption = "Processing... 0%"

Me.Repaint

DoEvents

End Sub

Option Explicit


'========================================================
' PROGRESS BAR UPDATE
'========================================================

Private Sub UpdateProgress(ByVal P As Long)

    If P < 0 Then P = 0
    If P > 100 Then P = 100

    frmProgress.Label2.Width = frmProgress.Label1.Width * P / 100
    frmProgress.Caption = "Processing... " & P & "%"
    frmProgress.Repaint
    DoEvents

End Sub


'========================================================
' SMOOTH PROGRESS
'========================================================

Private Sub SmoothProgress(ByVal FromP As Long, ByVal ToP As Long)

    Dim P As Long

    For P = FromP To ToP

        UpdateProgress P

        'Small animation delay
        Dim StartTime As Double
        StartTime = Timer

        Do While Timer - StartTime < 0.08
            DoEvents
        Loop

    Next P

End Sub


'========================================================
' MAIN MACRO
'========================================================

Sub Macro1()

    '----------------------------------------------------
    ' START
    '----------------------------------------------------

    frmProgress.Show vbModeless

    UpdateProgress 0

    Application.ScreenUpdating = False


    '====================================================
    ' PART 1
    ' PROGRESS: 0% - 5%
    '====================================================

    'PASTE OFFICE MACRO PART 1 HERE

    SmoothProgress 1, 5


    '====================================================
    ' PART 2
    ' PROGRESS: 5% - 10%
    '====================================================

    'PASTE OFFICE MACRO PART 2 HERE

    SmoothProgress 6, 10


    '====================================================
    ' PART 3
    ' PROGRESS: 10% - 15%
    '====================================================

    'PASTE OFFICE MACRO PART 3 HERE

    SmoothProgress 11, 15


    '====================================================
    ' PART 4
    ' PROGRESS: 15% - 20%
    '====================================================

    'PASTE OFFICE MACRO PART 4 HERE

    SmoothProgress 16, 20


    '====================================================
    ' PART 5
    ' PROGRESS: 20% - 25%
    '====================================================

    'PASTE OFFICE MACRO PART 5 HERE

    SmoothProgress 21, 25


    '====================================================
    ' PART 6
    ' PROGRESS: 25% - 30%
    '====================================================

    'PASTE OFFICE MACRO PART 6 HERE

    SmoothProgress 26, 30


    '====================================================
    ' PART 7
    ' PROGRESS: 30% - 35%
    '====================================================

    'PASTE OFFICE MACRO PART 7 HERE

    SmoothProgress 31, 35


    '====================================================
    ' PART 8
    ' PROGRESS: 35% - 40%
    '====================================================

    'PASTE OFFICE MACRO PART 8 HERE

    SmoothProgress 36, 40


    '====================================================
    ' PART 9
    ' PROGRESS: 40% - 45%
    '====================================================

    'PASTE OFFICE MACRO PART 9 HERE

    SmoothProgress 41, 45


    '====================================================
    ' PART 10
    ' PROGRESS: 45% - 50%
    '====================================================

    'PASTE OFFICE MACRO PART 10 HERE

    SmoothProgress 46, 50


    '====================================================
    ' PART 11
    ' PROGRESS: 50% - 55%
    '====================================================

    'PASTE OFFICE MACRO PART 11 HERE

    SmoothProgress 51, 55


    '====================================================
    ' PART 12
    ' PROGRESS: 55% - 60%
    '====================================================

    'PASTE OFFICE MACRO PART 12 HERE

    SmoothProgress 56, 60


    '====================================================
    ' PART 13
    ' PROGRESS: 60% - 65%
    '====================================================

    'PASTE OFFICE MACRO PART 13 HERE

    SmoothProgress 61, 65


    '====================================================
    ' PART 14
    ' PROGRESS: 65% - 70%
    '====================================================

    'PASTE OFFICE MACRO PART 14 HERE

    SmoothProgress 66, 70


    '====================================================
    ' PART 15
    ' PROGRESS: 70% - 75%
    '====================================================

    'PASTE OFFICE MACRO PART 15 HERE

    SmoothProgress 71, 75


    '====================================================
    ' PART 16
    ' PROGRESS: 75% - 80%
    '====================================================

    'PASTE OFFICE MACRO PART 16 HERE

    SmoothProgress 76, 80


    '====================================================
    ' PART 17
    ' PROGRESS: 80% - 85%
    '====================================================

    'PASTE OFFICE MACRO PART 17 HERE

    SmoothProgress 81, 85


    '====================================================
    ' PART 18
    ' PROGRESS: 85% - 90%
    '====================================================

    'PASTE OFFICE MACRO PART 18 HERE

    SmoothProgress 86, 90


    '====================================================
    ' PART 19
    ' PROGRESS: 90% - 95%
    '====================================================

    'PASTE OFFICE MACRO PART 19 HERE

    SmoothProgress 91, 95


    '====================================================
    ' PART 20
    ' PROGRESS: 95% - 99%
    '====================================================

    'PASTE OFFICE MACRO PART 20 HERE

    SmoothProgress 96, 99


    '====================================================
    ' FINAL
    ' PROGRESS: 100%
    '====================================================

    Application.ScreenUpdating = True

    UpdateProgress 100

    Application.Wait Now + TimeValue("00:00:01")

    frmProgress.Hide

End Sub

Tarun Kumar 0 Reputation points

2026-10-07T17:21:24.92+00:00

TarunVBA2026PB20
Barry Schwarz 6,271 Reputation points

2026-10-07T18:01:34.1666667+00:00

Did you have a question?

1 answer

Your answer

Tarun Kumar 0 Reputation points

2026-10-07T17:21:24.92+00:00

TarunVBA2026PB20
Barry Schwarz 6,271 Reputation points

2026-10-07T18:01:34.1666667+00:00

Did you have a question?

Answer 1

Hi @Tarun Kumar ,

Thank you for sharing the code sample.

After reviewing the VBA code, I do not see any obvious syntax issues or logic errors in the progress bar implementation itself. The UserForm is initialized correctly, the progress bar width is updated based on the percentage value, and DoEvents/Repaint are being used to keep the UI responsive during processing.

One observation is that the progress values are manually advanced through the SmoothProgress procedure rather than being tied to the actual completion status of the underlying tasks. As a result, the displayed percentage may not accurately reflect real processing progress if certain sections of the macro take significantly longer than others.

Additionally, the code currently does not include error handling. If an error occurs in one of the macro sections, the progress form may remain visible or stop updating unexpectedly.

Based on the code provided, the progress bar implementation appears reasonable; however, I cannot determine whether there is a defect without understanding the specific behavior being observed. Can you please provide:

The exact issue encountered.
Any error messages received.
Whether the progress bar is not appearing, not updating, freezing, or behaving unexpectedly.
The Excel version being used.

With those details, I can investigate further and determine whether the issue is related to the progress bar code itself or to one of the macro sections being executed.

If you found my response helpful or informative, I would greatly appreciate it if you could follow this guide for your confirmation. Thank you.