Results 1 to 2 of 2
Thread: Show Status bar running code
2012-05-19, 12:34 #1
- Join Date
- Jun 2011
- Thanked 0 Times in 0 Posts
Show Status bar running code
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
2012-05-19, 13:23 #2
- Join Date
- Mar 2002
- Newcazzle, UK
- Thanked 484 Times in 461 Posts
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.