How to use rectangle as progress bar

[ashni]

Can i Use rectangle shape as a progress bar showing progress of my work.
I don't want to use cells as progress bar as my application has the requirement that the shape should move from column to column.
I want to do it using macro.

 

[gallen]

Maybe this thread will help:
http://www.mrexcel.com/forum/excel-questions/612091-status-bar-percentage-bar.html

I've done one using a label. In essence you are just adjusting the width of the shape as the progress increases.

 

[ashni]

Percentage bar... Can u tell me how did u do it?

 

[gallen]

Sure, I used a UserForm named frmBar that popped up and expanded a label and updated the Form's Caption to show the %. I've cut all the code that does what I was using it for. this just shows the updating procedure.

lRow = current row
lTotalRows = total number of rows I am looping through
iPct = Percentage of rows I have done so far

Sub PerformUpdate()
    For lRow = 2 To lTotalRows            
         iPct = Round(lRow /lTotalRows* 100)'Divide current Row by total rows
         UpdateProgress iPct
    Next lRow
End Sub

The 'UpdateProgress' Procedure looks like this

Private Sub UpdateProgress(iPct As Integer)'update to progress bar form with the current % completed

    frmBar.frame_Bar.Caption = frmBar.sProcedure & " - " & iPct & "% Complete"
    frmBar.lblBar.Width = iPct * 2 'expands the bar by 2 points every %
    
    DoEvents
End Sub

If using a UserForm ensure you call the 'PerformUpdate' procedure from within the form

The UpdateProgress was stored in a module

Hope this helps, there are several tutorials on creating a progress bar in VBA just Google them

 

[ashni]

thanks gallen!!
You gave me an idea to work with it!!

 

[gallen]

No problem. Hope it works out.