Progress Bar

G

Guest

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?
 
B

Bob Phillips

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.
 
G

Guest

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
 
B

Bob Phillips

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.
 
G

Guest

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.
 
G

Guest

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
 
B

Bob Phillips

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

But ...

Try adding this before your code

Public UserCancelled As Boolean
 
G

Guest

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!
 
G

Guest

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
 
R

Robin Hammond

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
 
G

Guest

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?
 
B

Bob Phillips

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.
 
G

Guest

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.
 
G

Guest

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:
 
R

Robin Hammond

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
 
B

Bob Phillips

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help, Progress bar with multiple macros 2
Updated Modeless Progress Bar Class Available 1
Still Struggling with Progress Bar Logic 4
Using progress bar with existing loop 1
Still Struggling with Progress Bar Logic 1
hide progress bar when using ExportAsFixedFormat for printing to PDF 1
ActiveX ProgressBar - Scrolling Error disables color update 1
Routine too fast for Form to Update? 1

Top