Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Thanked 0 Times in 0 Posts

    Show Status bar running code

    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.

    HTML Code:
    Option Explicit
    Private Sub UserForm_Activate()
        Application.Cursor = xlWait
        ProgressBar.MousePointer = fmMousePointerHourGlass
        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
            Next y
            ProgressBar.TextBox2.Width = (x / Total1) * 200
            ProgressBar.Label1.Caption = "Updating: " & x & " of " & Total1
        Next x
    End Sub
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Newcazzle, UK
    Thanked 651 Times in 619 Posts
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts