Progress Bar

John · Jul 6, 2005

I was looking at a previous post to get a progress bar to work while running
a lengthy macro. Below is the post on how to incorporate the progress bar
into your code. That works fine expect for one thing. There is a cancel
button on the progress bar, and when it is pushed the rest of my code
continues running. How do I end my code if I press cancel on the progress
bar?

It would look something like this

Sub Main
Dim PB as clsProgBar
Set PB = new clsProgBar

'if your subs are being called from a userform, hide the form first using
Me.Hide
With PB

.title = "some title"
.caption1 = "Executing, this may take a while"
.caption2 = "Doing task 1"
.show
doevents

end with

Sub1 x,y,z
pb.progress = 10
pb.caption2 = "doing task 2"

Sub2 a,b,c,d
PB.progress = 20
pb.caption2 = "doing task 3"

'etc,
'and at the end

Sub10
PB.finish

'if called from a userform, show the form again, using me.show
End Sub

Again, How do I end my code if I press cancel on the progress bar?

Bob Phillips · Jul 6, 2005

You need to look at the class module and see what it does when the cancel
button is pressed. It will probably set a property, or could be changed to
set a property. You will need to then read this property in your code and
stop the execution.

John · Jul 6, 2005

Bob,

Here is the class Module code... any ideas thanks again for the help.

'PROGRESS BAR CLASS

'REQUIRES INCLUSION OF frmProgress Form

'COPYRIGHT of the AUTHOR: ROBIN HAMMOND

'UPDATED: 16 September 2003

'PURPOSE: Provides a modeless user form showing progress bar, title and three
'captions in Excel 2000 or higher. Now handles Excel 97 by using same methods
'with progress messages written to the statusbar

'LEGAL: You may use this code as is or with modifications in your programs
'No commercial use or sale of this code or derivative works is permitted
'under any circumstances without the express permission of the author.
'The author is likely to say yes if you ask nicely though.
'You may redistribute this workbook in its original form only, as
'first received from the Enhanced Datasystems website.
'For further information, please visit www.enhanceddatasystems.com

'The colour shading technique on the progress bar was done by
'Jamie Collins in response to a newsgroup challenge. Thanks Jamie.

Public DisableCancel As Boolean
Public Title As String

Private nVersion As Integer

Private strStatus As String
Private strStat1 As String
Private strStat2 As String
Private strStat3 As String
Private strProgress As String

Property Let Caption1(strCaption As String)

If nVersion < 9 Then

strStat1 = strCaption
UpdateStatus

Else

#If VBA6 Then

frmProgress.lblMsg1.Caption = strCaption
DoEvents

#End If

End If

End Property

Property Let Caption2(strCaption As String)

If nVersion < 9 Then

strStat2 = strCaption
UpdateStatus

Else

#If VBA6 Then

frmProgress.lblMsg2.Caption = strCaption
DoEvents

#End If

End If

End Property

Property Let Caption3(strCaption As String)

If nVersion < 9 Then

strStat3 = strCaption
UpdateStatus

Else

#If VBA6 Then

frmProgress.lblMsg3.Caption = strCaption
DoEvents

#End If

End If

End Property

Sub Finish()

If nVersion < 9 Then

Application.StatusBar = ""
Application.StatusBar = False

Else

#If VBA6 Then

Unload frmProgress

#End If

End If

End Sub

Sub Hide()

If nVersion < 9 Then

Application.StatusBar = ""
Application.StatusBar = False

Else

#If VBA6 Then

frmProgress.Hide

#End If

End If

End Sub

Property Let Progress(nWidth As Integer)
Dim nProgress As Integer

If nVersion < 9 Then

strProgress = CStr(nWidth)
UpdateStatus

Else

#If VBA6 Then

If nWidth > 100 Then nWidth = 100

If nWidth < 0 Then nWidth = 0

With frmProgress.imgProgFore

.Width = 200 - Int(nWidth * 2)
.Left = 12 + Int(nWidth * 2)

End With

DoEvents

#End If

End If

End Property

Sub Reset()

If nVersion < 9 Then

Application.StatusBar = ""
Application.StatusBar = False

Else

#If VBA6 Then

Title = ""
frmProgress.lblMsg1.Caption = ""
frmProgress.lblMsg2.Caption = ""
frmProgress.lblMsg3.Caption = ""
DisableCancel = False

#End If

End If

End Sub

Sub Show()

If nVersion < 9 Then

'probably best to leave the title out of this

Else

#If VBA6 Then

With frmProgress

If DisableCancel = True Then

.Width = 228
.cmdCancel.Enabled = False

End If

.Caption = Title
.Show vbModeless

End With

#End If

End If

End Sub

Private Sub Class_Initialize()

nVersion = Val(Application.Version)

End Sub

Private Sub Class_Terminate()

If nVersion < 9 Then Application.StatusBar = False

End Sub

Private Sub UpdateStatus()
Dim strStatus As String

strStatus = strStat1
If strStat2 <> "" Then strStatus = strStatus & ", " & strStat2
If strStat3 <> "" Then strStatus = strStatus & ", " & strStat3

If strProgress <> "" Then strStatus = strStatus & ", " & strProgress & "%"

Application.StatusBar = strStatus

End Sub

Bob Phillips · Jul 6, 2005

I've had a look at Robin's site and the whole demo.

What he does is to declare a Public variable of type Boolean in the module
that does the work, your code module. The form sets that variable to True if
the Cancel button is pressed.

You need to test that variable in your code, and when it becomes true, you
exit.

John · Jul 6, 2005

Thanks for the help Bob, but I am new to VBA and don't even know what half of
the things you said are. Could you give me an example? That would help
alot. Thanks.

Bob Phillips · Jul 6, 2005

John,

Can you post me your workbook and I will implement it for you?

John · Jul 6, 2005

Sub Bloomberg_Searches()
'
'
' Macro recorded 6/30/2005 by john
'

'
Dim PB As clsProgBar
Set PB = New clsProgBar
With PB
PB.Title = "Performing Searches in Bloomberg"
PB.Caption1 = "Searches are running, please wait..."
PB.Show
DoEvents
If UserCancelled = True Then GoTo EndRoutine

AppActivate ("1-BLOOMBERG"), 2
PB.Progress = 12.5
PB.Caption2 = "Performing 1st Agency Search"
' Ag
SendKeys ("srch~")
SendKeys ("1~")
SendKeys ("8~")
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys Format(Range("x3").Value, "MM/DD/YYYY")
SendKeys Format(Range("x3").Value, "MM/DD/YYYY")
SendKeys ("~")
SendKeys ("srch~")
SendKeys ("1~")
SendKeys ("1~")
Application.Wait Now + TimeSerial(0, 0, 7)
' Corp
PB.Progress = 25
PB.Caption2 = "Performing 1st Corporate Search"
SendKeys ("srch~")
SendKeys ("2~")
SendKeys ("8~")
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys Format(Range("x3").Value, "MM/DD/YYYY")
SendKeys Format(Range("x3").Value, "MM/DD/YYYY")
SendKeys ("~")
SendKeys ("srch~")
SendKeys ("2~")
SendKeys ("1~")
Application.Wait Now + TimeSerial(0, 0, 7)
' Start of 2nd Search Date
' Ag
PB.Progress = 37.5
PB.Caption2 = "Performing 2nd Agency Search"
SendKeys ("srch~")
SendKeys ("1~")
SendKeys ("8~")
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys Format(Range("y3").Value, "MM/DD/YYYY")
SendKeys Format(Range("y3").Value, "MM/DD/YYYY")
SendKeys ("~")
SendKeys ("srch~")
SendKeys ("1~")
SendKeys ("1~")
Application.Wait Now + TimeSerial(0, 0, 7)
' Corp
PB.Progress = 50
PB.Caption2 = "Performing 2nd Corporate Search"
SendKeys ("srch~")
SendKeys ("2~")
SendKeys ("8~")
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys Format(Range("y3").Value, "MM/DD/YYYY")
SendKeys Format(Range("y3").Value, "MM/DD/YYYY")
SendKeys ("~")
SendKeys ("srch~")
SendKeys ("2~")
SendKeys ("1~")
Application.Wait Now + TimeSerial(0, 0, 7)
' Start of 3rd Search
' Ag
PB.Progress = 62.5
PB.Caption2 = "Performing 3rd Agency Search"
SendKeys ("srch~")
SendKeys ("1~")
SendKeys ("8~")
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys Format(Range("z3").Value, "MM/DD/YYYY")
SendKeys Format(Range("z3").Value, "MM/DD/YYYY")
SendKeys ("~")
SendKeys ("srch~")
SendKeys ("1~")
SendKeys ("1~")
Application.Wait Now + TimeSerial(0, 0, 7)
' Corp
PB.Progress = 75
PB.Caption2 = "Performing 3rd Corporate Search"
SendKeys ("srch~")
SendKeys ("2~")
SendKeys ("8~")
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys Format(Range("z3").Value, "MM/DD/YYYY")
SendKeys Format(Range("z3").Value, "MM/DD/YYYY")
SendKeys ("~")
SendKeys ("srch~")
SendKeys ("2~")
SendKeys ("1~")
Application.Wait Now + TimeSerial(0, 0, 7)
' Start of 4th Search
' Ag
PB.Progress = 87.5
PB.Caption2 = "Performing 4th Agency Search"
SendKeys ("srch~")
SendKeys ("1~")
SendKeys ("8~")
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys Format(Range("aa3").Value, "MM/DD/YYYY")
SendKeys Format(Range("aa3").Value, "MM/DD/YYYY")
SendKeys ("~")
SendKeys ("srch~")
SendKeys ("1~")
SendKeys ("1~")
Application.Wait Now + TimeSerial(0, 0, 7)
' Corp
PB.Progress = 95
PB.Caption2 = "Performing 4th Corporate Search"
SendKeys ("srch~")
SendKeys ("2~")
SendKeys ("8~")
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys Format(Range("aa3").Value, "MM/DD/YYYY")
SendKeys Format(Range("aa3").Value, "MM/DD/YYYY")
SendKeys ("~")
PB.Progress = 98
PB.Caption2 = "Performing 4th Corporate Search"
SendKeys ("srch~")
SendKeys ("2~")
SendKeys ("1~")
SendKeys ("rpt~")
EndRoutine:
PB.Finish

End Sub

Bob Phillips · Jul 6, 2005

Doesn't help me, I don't have that App.

But ...

Try adding this before your code

Public UserCancelled As Boolean

John · Jul 6, 2005

Well that doesn't help either... no matter if it is before the code, in the
code, anywhere...

Thanks for the idea.

Seems like it shouldn't be to hard to tell the code to stop running... but I
will look more.

Please feel free to help!

Greg Wilson · Jul 7, 2005

Point 1:

Rob van Gelder has two progress bars at his site that don't have Cancel
buttons:
http://homepages.paradise.net.nz/~robree/excel/

I have my own. Mine replicates the raised (e.g. toolbar) and sunken (e.g.
textbox) 3D special effects but is entirely constructed of Excel drawing
objects - i.e. it looks like an ActiveX control but is native to Excel. For
simplicity, I suggest using one of Rob's. If for some reason they arn't
suitable, you're welcome to try mine.

Point 2:

Your code appears that it could be greatly abbreviated. Instead of repeating
the Application.SendKeys "{Tab}" line I believe either of these two options
will work. You'll have to experiment:
i) Application.SendKeys "{Tab}{Tab}{Tab}{Tab}{Tab}{Tab}....."
ii) For i = 1 To 10
Application.SendKeys "{Tab}"
Next

Regards,
Greg

Robin Hammond · Jul 7, 2005

John,

I missed this yesterday. Thanks for the help Bob. It is very simple, and
would go something like this:

'this goes in your main module
Public UserCancelled As Boolean

Sub ProgBarDemo()
Dim PB As clsProgBar
Dim nCounter As Integer
Dim lWaitCount As Long

Set PB = New clsProgBar
With PB
.Title = "Enhanced Datasystems Progress Bar"
.Caption2 = "This is caption 2"
.Caption3 = "This is caption 3"
.Show
For nCounter = 0 To 100
.Progress = nCounter
.Caption1 = "Progress message " & CStr(nCounter)
For lWaitCount = 0 To 1000000
If UserCancelled = True Then GoTo EndRoutine
Next lWaitCount
Next nCounter
EndRoutine:
.Finish
End With
Set PB = Nothing
End Sub

Alternatively, if you do not want to display the cancel button, when you
initialise the class in the code above, just do this

With PB
'add this line
.DisableCancel = TRUE
'rest of your code here

Post back if you are still having trouble.

Robin Hammond
www.enhanceddatasystems.com

John · Jul 7, 2005

Robin, thanks for the help.

I am getting the following error when I try to run the macro now.

Compile Error:
Ambiguious Name Detected: UserName Cancelled

Do I have a command in the wrong place?

Bob Phillips · Jul 7, 2005

That is because you have defined it (at least) twice.

Do a search and delete one. The one that you need should be a Public Boolean
variable, declared at the start of a module, outside of any macro.

John · Jul 7, 2005

Bob,

That took away my error, Thanks. I still cannot use the cancel button... I
press it, and the code then does the sendkeys commands into my excel workbook
rather than quitting the code itself.

Sorry this is seeming to be a bigger issue than it should be.

Thanks again.

Bob Phillips · Jul 7, 2005

Do you know how to debug code, stepping through?

John · Jul 7, 2005

No, unless a prompt comes up and gives the option to debug... then it
highlights the bad command line in yellow.

My code is not doing that... it runs, but as I said, it doesn't end the sub
if cancel is pressed.

Where should this command line be placed in the code?
If UserCancelled = True Then GoTo EndRoutine:

Bob Phillips · Jul 7, 2005

Are you testing for UserCancelled in your loops?

John · Jul 7, 2005

no, how would I do that?

Bob Phillips said:
Are you testing for UserCancelled in your loops?

Robin Hammond · Jul 8, 2005

John,

The serial structure of your code means that you will have to test several
times. In the longer term if you are going to build Bloomberg based systems,
you might want to have a look at their api. I think you can find details in
their help section somewhere.

In the meantime, your code would go something like this:

Public UserCancelled as Boolean

Sub BloombergCall()

Dim PB As clsProgBar
Set PB = New clsProgBar
With PB
PB.Title = "Performing Searches in Bloomberg"
PB.Caption1 = "Searches are running, please wait..."
PB.Show
DoEvents

AppActivate ("1-BLOOMBERG"), 2
PB.Progress = 12.5
PB.Caption2 = "Performing 1st Agency Search"
If UserCancelled = TRUE Then Goto EndRoutine
' Ag
SendKeys ("srch~")
SendKeys ("1~")
SendKeys ("8~")
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys Format(Range("x3").Value, "MM/DD/YYYY")
SendKeys Format(Range("x3").Value, "MM/DD/YYYY")
SendKeys ("~")
SendKeys ("srch~")
SendKeys ("1~")
SendKeys ("1~")
If UserCancelled = TRUE Then Goto EndRoutine
Application.Wait Now + TimeSerial(0, 0, 7)
' Corp
PB.Progress = 25
PB.Caption2 = "Performing 1st Corporate Search"
SendKeys ("srch~")
SendKeys ("2~")
SendKeys ("8~")
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys Format(Range("x3").Value, "MM/DD/YYYY")
SendKeys Format(Range("x3").Value, "MM/DD/YYYY")
SendKeys ("~")
SendKeys ("srch~")
SendKeys ("2~")
SendKeys ("1~")
If UserCancelled = TRUE Then Goto EndRoutine
Application.Wait Now + TimeSerial(0, 0, 7)
' Start of 2nd Search Date
' Ag
PB.Progress = 37.5
PB.Caption2 = "Performing 2nd Agency Search"
SendKeys ("srch~")
SendKeys ("1~")
SendKeys ("8~")
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys Format(Range("y3").Value, "MM/DD/YYYY")
SendKeys Format(Range("y3").Value, "MM/DD/YYYY")
SendKeys ("~")
SendKeys ("srch~")
SendKeys ("1~")
SendKeys ("1~")
If UserCancelled = TRUE Then Goto EndRoutine
Application.Wait Now + TimeSerial(0, 0, 7)
' Corp
PB.Progress = 50
PB.Caption2 = "Performing 2nd Corporate Search"
SendKeys ("srch~")
SendKeys ("2~")
SendKeys ("8~")
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys Format(Range("y3").Value, "MM/DD/YYYY")
SendKeys Format(Range("y3").Value, "MM/DD/YYYY")
SendKeys ("~")
SendKeys ("srch~")
SendKeys ("2~")
SendKeys ("1~")
If UserCancelled = TRUE Then Goto EndRoutine
Application.Wait Now + TimeSerial(0, 0, 7)
' Start of 3rd Search
' Ag
PB.Progress = 62.5
PB.Caption2 = "Performing 3rd Agency Search"
SendKeys ("srch~")
SendKeys ("1~")
SendKeys ("8~")
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys Format(Range("z3").Value, "MM/DD/YYYY")
SendKeys Format(Range("z3").Value, "MM/DD/YYYY")
SendKeys ("~")
SendKeys ("srch~")
SendKeys ("1~")
SendKeys ("1~")
If UserCancelled = TRUE Then Goto EndRoutine
Application.Wait Now + TimeSerial(0, 0, 7)
' Corp
PB.Progress = 75
PB.Caption2 = "Performing 3rd Corporate Search"
SendKeys ("srch~")
SendKeys ("2~")
SendKeys ("8~")
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys Format(Range("z3").Value, "MM/DD/YYYY")
SendKeys Format(Range("z3").Value, "MM/DD/YYYY")
SendKeys ("~")
SendKeys ("srch~")
SendKeys ("2~")
SendKeys ("1~")
If UserCancelled = TRUE Then Goto EndRoutine
Application.Wait Now + TimeSerial(0, 0, 7)
' Start of 4th Search
' Ag
PB.Progress = 87.5
PB.Caption2 = "Performing 4th Agency Search"
SendKeys ("srch~")
SendKeys ("1~")
SendKeys ("8~")
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys Format(Range("aa3").Value, "MM/DD/YYYY")
SendKeys Format(Range("aa3").Value, "MM/DD/YYYY")
SendKeys ("~")
SendKeys ("srch~")
SendKeys ("1~")
SendKeys ("1~")
If UserCancelled = TRUE Then Goto EndRoutine
Application.Wait Now + TimeSerial(0, 0, 7)
' Corp
PB.Progress = 95
PB.Caption2 = "Performing 4th Corporate Search"
SendKeys ("srch~")
SendKeys ("2~")
SendKeys ("8~")
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys Format(Range("aa3").Value, "MM/DD/YYYY")
SendKeys Format(Range("aa3").Value, "MM/DD/YYYY")
SendKeys ("~")
PB.Progress = 98
PB.Caption2 = "Performing 4th Corporate Search"
SendKeys ("srch~")
SendKeys ("2~")
SendKeys ("1~")
SendKeys ("rpt~")
EndRoutine:
PB.Finish
Set PB = Nothing
End Sub

Robin Hammond
www.enhanceddatasystems.com

Bob Phillips · Jul 8, 2005

I'm also sure that you could get that serial code into a loop, simplifying
the problem

Option Explicit

Public UserCancelled As Boolean

Sub BloombergCall()

Dim pb As clsProgBar
Set pb = New clsProgBar
With pb
pb.Title = "Performing Searches in Bloomberg"
pb.Caption1 = "Searches are running, please wait..."
pb.Show
DoEvents

'initialise everything
AppActivate ("1-BLOOMBERG"), 2
' Ag
Call SearchLoop("Performing 1st Agency Search", 12.5, "-1")
If UserCancelled = True Then GoTo EndRoutine
Application.Wait Now + TimeSerial(0, 0, 7)
' Corp
pb.Progress = 25
Call SearchLoop("Performing 1st Corporate Search", 25, "-2")
If UserCancelled = True Then GoTo EndRoutine
Application.Wait Now + TimeSerial(0, 0, 7)
' Start of 2nd Search Date
' Ag
Call SearchLoop("Performing 2nd Agency Search", 37.5, "-1")
If UserCancelled = True Then GoTo EndRoutine
Application.Wait Now + TimeSerial(0, 0, 7)
' Corp
Call SearchLoop("Performing 2nd Corporate Search", 50, "-2")
If UserCancelled = True Then GoTo EndRoutine
Application.Wait Now + TimeSerial(0, 0, 7)
' Start of 3rd Search
' Ag
Call SearchLoop("Performing 3rd Agency Search", 62.5, "-1")
If UserCancelled = True Then GoTo EndRoutine
Application.Wait Now + TimeSerial(0, 0, 7)
' Corp
Call SearchLoop("Performing 3rd Corporate Search", 75, "-2")
If UserCancelled = True Then GoTo EndRoutine
Application.Wait Now + TimeSerial(0, 0, 7)
' Start of 4th Search
' Ag
Call SearchLoop("Performing 4th Agency Search", 87.5, "-1")
If UserCancelled = True Then GoTo EndRoutine
Application.Wait Now + TimeSerial(0, 0, 7)
' Corp
Call SearchLoop("Performing 4th Corporate Search", 95, "-2")
pb.Progress = 98
pb.Caption2 = "Performing 4th Corporate Search"
SendKeys ("rpt~")
EndRoutine:
pb.Finish
Set pb = Nothing
End Sub

Function SearchLoop(pbCaption As String, pbScal As Double, pbChange As
String)
SearchLoop = False
If UserCancelled Then
SearchLoop = True
Else
pb.Caption2 = pbCaption
pb.Progress = pbScale
SendKeys ("srch~")
SendKeys ("1~")
SendKeys ("8~")
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys Format(Range("x3").Value, "MM/DD/YYYY")
SendKeys Format(Range("x3").Value, "MM/DD/YYYY")
SendKeys ("~")
SendKeys ("srch~")
SendKeys (pbChange)
SendKeys ("1~")
If UserCancelled = True Then GoTo EndRoutine
Application.Wait Now + TimeSerial(0, 0, 7)
End If
End Function

hide progress bar when using ExportAsFixedFormat for printing to PDF	1	Jun 30, 2012
Help, Progress bar with multiple macros	2	Jul 26, 2006
Still Struggling with Progress Bar Logic	4	Oct 13, 2007
Still Struggling with Progress Bar Logic	1	Oct 13, 2007
Updated Modeless Progress Bar Class Available	1	Sep 17, 2003
Using progress bar with existing loop	1	Nov 17, 2004
Routine too fast for Form to Update?	1	Jul 8, 2011
JW Progress Bar after DoubleClick event	0	Apr 1, 2010

Progress Bar

John

Bob Phillips

John

Bob Phillips

John

Bob Phillips

John

Bob Phillips

John

Greg Wilson

Robin Hammond

John

Bob Phillips

John

Bob Phillips

John

Bob Phillips

John

Robin Hammond

Bob Phillips

Ask a Question

Similar Threads