Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    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
        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
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 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.

    zeddy

Posting Permissions

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