Show Status bar running code

[farrukh]

HI All,


I have googled a code which shows the status bar on executing code. I need to Place my vba code to show the running progress where i place or call my module. The code and worksheet is attached.

Option Explicit

Private Sub UserForm_Activate()

    Application.Cursor = xlWait
    ProgressBar.MousePointer = fmMousePointerHourGlass
    DoEvents
    Call CalculateData
   
    Application.Cursor = xlDefault
    Unload Me

End Sub

Private Sub UserForm_Initialize()

    TextBox2.Left = TextBox1.Left
    TextBox2.Top = TextBox1.Top + 3
    TextBox4.Left = TextBox3.Left
    TextBox4.Top = TextBox3.Top + 3
    TextBox2.Width = 0
    TextBox4.Width = 0

End Sub

Sub CalculateData()

Dim Total1          As Long
Dim Total2          As Long
Dim x               As Long
Dim y               As Long
Dim MyTimer         As Double

    Call ExcelTest
    Total1 = 20
    Total2 = 1000
    
    For x = 1 To Total1
        For y = 1 To Total2
            MyTimer = Timer
            ProgressBar.TextBox4.Width = (y / Total2) * 200
            ProgressBar.Label2.Caption = "Calculating Data: " & y & " of " & Total2
            DoEvents
        Next y
        ProgressBar.TextBox2.Width = (x / Total1) * 200
        ProgressBar.Label1.Caption = "Updating: " & x & " of " & Total1
    Next x

End Sub

[zeddy]

Hi Farrukh

The sample file you have shows two progress bars.
The first sample progress bar shows an update process which has, for example, 20 steps.
This is like processing 20 reports, or processing 20 data files for example.

For each of these 20 sample steps, there is another process that has to run.
In the example, it is a calculation process that takes 1000 steps i.e. runs from 1 to 1000.

So, to incorporate this code into your own VBA routines, you need to have a process that can be specifically 'counted'.
For example, if it is to process xx number of datafiles, you need to be able to determine what the value of the number xx is before you start the progress bar. For example, you could use a separate routine to first count how many report files there are to be processed in a particular folder for example.

zeddy