Can anybody give me sample on how to incorporate a progress bar on my existing macro. I manage to get a source code in diplaying progress bar but i can only run the progress bar macro before or after my existing macro. I wanted to run the progress bar macro while the other macro is running. Is it possible? can anybody help me?
Progress bar on running macro
-
rhaykho -
June 24, 2004 at 5:52 AM -
Closed
-
-
-
Hi rhaykho
Welcome to ozgrid.com
Without seeing your code, you question is impossible to answer. Anyway, one of our resident Excel gurus (Andy Pope) has posted some really cool code.Progress bars
-
Progress
can somebody help me incorporate one of the progress meter(posted by Andy Pope) on my macro (piping_report). I appreciate your help.
Thanks. -
Progress bars are only really suited to code that Loops. Try this method instead: Display Wait Message
-
My preferred method is to use Excel's application bar to give the user a message. I also change the cursor to show the Hourglass. I would also suggest that you switch off screen updating to prevent the screen flickering & to speed up your macro a little.Try adding this code:
Code
Display More' Written by royUK ' [url]www.excel-it.com[/url] 'switch of updating to speed your code & stop irritating flickering Application.ScreenUpdating = False 'Use the Status Bar to inform user of the macro's progress 'change the cursor to hourglass Application.Cursor = xlWait ' makes sure that the statusbar is visible Application.DisplayStatusBar = True 'add your message to status bar Application.StatusBar = "Formatting Report..." 'insert your code here 'restore default cursor Application.Cursor = xlDefault ' gives control of the statusbar back to the programme Application.StatusBar = False Application.ScreenUpdating = True End Sub
-
-
Re: Progress bar on running macro
Hello people
I have got some code in my application that basically opens a 'Master File' then saves the masterfile with a new name file1, file2 for example.
the code itself works fine but I want a progress bar to be shown.
I've been looking at Andy's examples but don't know how to encorporate it with my code.
I attached a small example of my files to show what I want. Open up MyApplication first.
Is it possible to have a progress bar while the code is running.
This is for an end of week routine that would need to run several modules. Can you have the bar displayed if running more that one module.
Hope all this makes sense.
CheersKev.
-
Re: Progress bar on running macro
Hi Ktrasler
Progress bar code requires it to be placed inside of a Loop. If your code has no looping done then the Progress bar will not be usable.
You could display a static message to the user though, download the example below
-
Re: Progress bar on running macro
Hi,
As Dave points out you need to have the progress code within a loop.
Try moving the CreateTeamsheet code inside the Go button event[vba]Sub Progress()
'
' Progress Bar
'
Dim lngIndex As Long
Dim sngPercent As Single
Dim intMax As Integer
Dim TEAMSHEETFILENAME As String
Dim lngTotal As Long
Dim iCOL As Integer
Dim iROW As Integer
Dim x
Workbooks.Open Filename:="C:\MASTERFILE.xls"
iCOL = 1
iROW = 1
Workbooks.Open Filename:="C:\MASTERFILE.xls"
With Workbooks("MYAPPLICATION.xls").Sheets("Sheet1")
lngTotal = .Range("A1").End(xlDown).Row + .Range("B1").End(xlDown).Row + .Range("C1").End(xlDown).Row
For x = 1 To 3
Do
TEAMSHEETFILENAME = .Cells(iROW, iCOL)
If TEAMSHEETFILENAME = "" Then Exit Do
Sheets("Sheet1").Range("B1") = TEAMSHEETFILENAME
ActiveWorkbook.SaveAs "C:\" & TEAMSHEETFILENAME & ".xls"
lngIndex = lngIndex + 1
sngPercent = lngIndex / lngTotal
ProgressStyle1 sngPercent, True
DoEvents
iROW = iROW + 1
Loop
iROW = 1
iCOL = iCOL + 1
Next x
End With
ActiveWorkbook.CloseEnd Sub[/vba]
-
Re: Progress bar on running macro
Hi Dave/Andy
Thanks for your replies
Dave,
I have used the DisplayWaitMessage quite a lot and i find it comes in very handy with fairly short code. I wanted to use a progress bar for this as the code will take a long time to run and I wanted to show how far and how long the code had left to run.
All of the modules that i would need to run for my end of week process use loops so I hope this will be achievable?
Andy,
I have put you code into my file, but nothing happens to the bar at all now it stays at 0% while all the code runs.
Am I missing something very simple?
Code
Display MoreOption Explicit Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Private Sub cmdGO_Click() Progress End Sub Private Sub UserForm_Initialize() labPg1.Tag = labPg1.Width labPg1.Width = 0 labPg1v.Caption = "" labPg1va.Caption = "" End Sub Sub ProgressStyle1(Percent As Single, ShowValue As Boolean) Const PAD = " " If ShowValue Then labPg1v.Caption = PAD & Format(Percent, "0%") labPg1va.Caption = labPg1v.Caption labPg1va.Width = labPg1.Width End If labPg1.Width = Int(labPg1.Tag * Percent) End Sub Sub Progress() Dim lngIndex As Long Dim sngPercent As Single Dim intMax As Integer Dim TEAMSHEETFILENAME As String Dim lngTotal As Long Dim iCOL As Integer Dim iROW As Integer Dim x Workbooks.Open Filename:="C:\MASTERFILE.xls" iCOL = 1 iROW = 1 Workbooks.Open Filename:="C:\MASTERFILE.xls" With Workbooks("MYAPPLICATION.xls").Sheets("Sheet1") lngTotal = .Range("A1").End(xlDown).Row + .Range("B1").End(xlDown).Row + .Range("C1").End(xlDown).Row For x = 1 To 3 Do TEAMSHEETFILENAME = .Cells(iROW, iCOL) If TEAMSHEETFILENAME = "" Then Exit Do Sheets("Sheet1").Range("B1") = TEAMSHEETFILENAME ActiveWorkbook.SaveAs "C:\" & TEAMSHEETFILENAME & ".xls" lngIndex = lngIndex + 1 sngPercent = lngIndex / lngTotal ProgressStyle1 sngPercent, True DoEvents iROW = iROW + 1 Loop iROW = 1 iCOL = iCOL + 1 Next x End With ActiveWorkbook.Close End Sub
Cheers again
Kev.
-
Re: Progress bar on running macro
Hi,
The way lngTotal is calculated fails due to column B only having 1 entry. This causes the total to be 65539 instead of 6. and even 6 as a percentage of 65K is 0.
This modification should sort you.
[vba]Sub Progress()
'
' Progress Bar
'
Dim lngIndex As Long
Dim sngPercent As Single
Dim intMax As Integer
Dim TEAMSHEETFILENAME As String
Dim lngTotal As Long
Dim iCOL As Integer
Dim iROW As Integer
Dim x
iCOL = 1
iROW = 1
Workbooks.Open Filename:="C:\temp\MASTERFILE.xls"
With Workbooks("MYAPPLICATION.xls").Sheets("Sheet1")
If .Range("A2") <> "" Then
lngTotal = .Range("a1").End(xlDown).Row
Else
lngTotal = 1
End If
If .Range("B2") <> "" Then
lngTotal = lngTotal + .Range("B1").End(xlDown).Row
Else
lngTotal = lngTotal + 1
End If
If .Range("c2") <> "" Then
lngTotal = lngTotal + .Range("C1").End(xlDown).Row
Else
lngTotal = lngTotal + 1
End If
For x = 1 To 3
Do
TEAMSHEETFILENAME = .Cells(iROW, iCOL)
If TEAMSHEETFILENAME = "" Then Exit Do
Sheets("Sheet1").Range("B1") = TEAMSHEETFILENAME
ActiveWorkbook.SaveAs "C:\temp\" & TEAMSHEETFILENAME & ".xls"
lngIndex = lngIndex + 1
sngPercent = lngIndex / lngTotal
ProgressStyle1 sngPercent, True
DoEvents
iROW = iROW + 1
Loop
iROW = 1
iCOL = iCOL + 1
Next x
End With
ActiveWorkbook.Close
End Sub[/vba] -
-
Re: Progress bar on running macro
Hi Andy
Thanks for that, its working great now.
Cheers
Kev.
-
Re: Progress bar on running macro
Hi Andy
Thank for share Idea its Working very Great .Thank.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!