How to add Process Bar

[nareshmedarmatila]

Hello Team,

Im trying to add Progress /Process bar to my macro!

I searched in google and few relevant posts here!

I found this link Progress meters, and this Progress Indicator in Excel VBA

As they said I added Frames and Labels in Userform, But my bad luck I dont know how to write code accordingly!

Please do help me how add a progress or process bar for to my Macro

this is Macro code ( I got this code from Mr.excel only)

Sub dispatch_by()
'
' Macro for Dispatch By
'
     If ActiveSheet.Name = "Sheet1" Then
      MsgBox "Cannot Run macro In This sheet. Try in Another Sheet"
      Exit Sub
   End If
   result = MsgBox("Replacing Existing Data with Dispatch By", vbOKCancel + vbQuestion, "QC_TOOL")
   If result = vbCancel Then
   Exit Sub
   Else
   Cells.Select
    Selection.ClearContents
    Cells.Select
    Selection.Cut
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
   Sheets("Sheet1").Range("A:A").Copy Range("A1")
   With Range("A:A")
      .EntireColumn.AutoFit
   End With
   Sheets("Sheet1").Range("B:B").Copy Range("B1")
   With Range("B:B")
      .EntireColumn.AutoFit
   End With
   Sheets("Sheet1").Range("I:I").Copy Range("C1")
   With Range("C:C")
      .EntireColumn.AutoFit
      .Select
     Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon _
        :=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array( _
        Array(1, 2), Array(2, 2), Array(3, 2)), TrailingMinusNumbers:=True
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Space Count"
    Range("D2").Select
    Range("C2:C" & Cells(Rows.Count, "C").End(3).Row).SpecialCells(xlCellTypeConstants).Offset(, 1).Formula = "=LEN(RC[-1])-LEN(SUBSTITUTE(RC[-1],"" "",""""))"
    Columns("F:F").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("H:H").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "First Name"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Middle Name"
   Range("I1").Select
    ActiveCell.FormulaR1C1 = "Last Name"
     Range("H1").Select
    ActiveCell.FormulaR1C1 = "Char Count"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Char Count"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Char Count"
    Columns("F:F").Select
    Selection.NumberFormat = "General"
    Range("F2").Select
    Range("E2:E" & Cells(Rows.Count, "E").End(3).Row).SpecialCells(xlCellTypeConstants).Offset(, 1).Formula = "=LEN(E2)"
    Columns("H:H").Select
    Selection.NumberFormat = "General"
    Range("H2").Select
    Range("G2:G" & Cells(Rows.Count, "G").End(3).Row).SpecialCells(xlCellTypeConstants).Offset(, 1).Formula = "=LEN(G2)"
    Columns("J:J").Select
    Selection.NumberFormat = "General"
    Range("J2").Select
    Range("I2:I" & Cells(Rows.Count, "I").End(3).Row).SpecialCells(xlCellTypeConstants).Offset(, 1).Formula = "=LEN(I2)"
    Range("A1:J1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Selection.Font.Bold = True
    Rows("1:1").Select
    Selection.AutoFilter
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("D2").Select
    End With
    End If
End Sub

Like this I have 18 Macros.. So When I run a macro I need a Process bar for every Individual macro

 

[nareshmedarmatila]

Steps Which I followed till


In Userform

1.Added the frame control. changed some properties of this frame control. Emptied the Caption field, Changed the Height to 24 and Width to 204.
2.Added the 1st label and placed it in the Frame control. Changed the name to Bar, BackColor to green, emptied the Caption field and Changed the Height to 20 and Width to 10.
3.Add the 2nd label and placed it above the Frame control. Changed the name to Text and changed the Caption to '0% Completed'.
4.Changed the caption of the Userform to Progress Indicator.


From here! I don't know How to move further!