|
Progress Bar
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? |
Progress Bar
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. -- HTH Bob Phillips "John" wrote in message ... 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? |
Progress Bar
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" wrote: 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. -- HTH Bob Phillips "John" wrote in message ... 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? |
Progress Bar
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. -- HTH Bob Phillips "John" wrote in message ... 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" wrote: 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. -- HTH Bob Phillips "John" wrote in message ... 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? |
Progress Bar
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" wrote: 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. -- HTH Bob Phillips "John" wrote in message ... 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" wrote: 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. -- HTH Bob Phillips "John" wrote in message ... 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 |
Progress Bar
John,
Can you post me your workbook and I will implement it for you? -- HTH Bob Phillips "John" wrote in message ... 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" wrote: 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. -- HTH Bob Phillips "John" wrote in message ... 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" wrote: 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. -- HTH Bob Phillips "John" wrote in message ... 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 |
Progress Bar
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" wrote: John, Can you post me your workbook and I will implement it for you? -- HTH Bob Phillips "John" wrote in message ... 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" wrote: 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. -- HTH Bob Phillips "John" wrote in message ... 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" wrote: 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 |
Progress Bar
Doesn't help me, I don't have that App.
But ... Try adding this before your code Public UserCancelled As Boolean -- HTH Bob Phillips "John" wrote in message ... 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" wrote: John, Can you post me your workbook and I will implement it for you? -- HTH Bob Phillips "John" wrote in message ... 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" wrote: 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. -- HTH Bob Phillips "John" wrote in message ... 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" wrote: 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 |
Progress Bar
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! "Bob Phillips" wrote: Doesn't help me, I don't have that App. But ... Try adding this before your code Public UserCancelled As Boolean -- HTH Bob Phillips "John" wrote in message ... 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~") |
Progress Bar
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 "John" wrote: 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! "Bob Phillips" wrote: Doesn't help me, I don't have that App. But ... Try adding this before your code Public UserCancelled As Boolean -- HTH Bob Phillips "John" wrote in message ... 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~") |
Progress Bar
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" wrote in message ... 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? |
Progress Bar
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? "Robin Hammond" wrote: 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" wrote in message ... 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? |
Progress Bar
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. -- HTH Bob Phillips "John" wrote in message ... 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? "Robin Hammond" wrote: 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" wrote in message ... 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? |
Progress Bar
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" wrote: 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. -- HTH Bob Phillips "John" wrote in message ... 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? "Robin Hammond" wrote: 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" wrote in message ... 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? |
Progress Bar
Do you know how to debug code, stepping through?
-- HTH Bob Phillips "John" wrote in message ... 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" wrote: 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. -- HTH Bob Phillips "John" wrote in message ... 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? "Robin Hammond" wrote: 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" wrote in message ... 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? |
Progress Bar
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" wrote: Do you know how to debug code, stepping through? -- HTH Bob Phillips "John" wrote in message ... 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" wrote: 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. -- HTH Bob Phillips "John" wrote in message ... 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? "Robin Hammond" wrote: 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" wrote in message ... 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? |
Progress Bar
Are you testing for UserCancelled in your loops?
-- HTH Bob Phillips "John" wrote in message ... 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" wrote: Do you know how to debug code, stepping through? -- HTH Bob Phillips "John" wrote in message ... 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" wrote: 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. -- HTH Bob Phillips "John" wrote in message ... 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? "Robin Hammond" wrote: 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" wrote in message ... 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? |
Progress Bar
no, how would I do that?
"Bob Phillips" wrote: Are you testing for UserCancelled in your loops? -- HTH Bob Phillips "John" wrote in message ... 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" wrote: Do you know how to debug code, stepping through? -- HTH Bob Phillips "John" wrote in message ... 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" wrote: 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. -- HTH Bob Phillips "John" wrote in message ... 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? "Robin Hammond" wrote: 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" wrote in message ... 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? |
Progress Bar
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 "John" wrote in message ... no, how would I do that? "Bob Phillips" wrote: Are you testing for UserCancelled in your loops? -- HTH Bob Phillips "John" wrote in message ... 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" wrote: Do you know how to debug code, stepping through? -- HTH Bob Phillips "John" wrote in message ... 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" wrote: 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. -- HTH Bob Phillips "John" wrote in message ... 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? "Robin Hammond" wrote: 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" wrote in message ... 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? |
Progress Bar
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 -- HTH Bob Phillips "Robin Hammond" wrote in message ... 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 "John" wrote in message ... no, how would I do that? "Bob Phillips" wrote: Are you testing for UserCancelled in your loops? -- HTH Bob Phillips "John" wrote in message ... 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" wrote: Do you know how to debug code, stepping through? -- HTH Bob Phillips "John" wrote in message ... 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" wrote: 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. -- HTH Bob Phillips "John" wrote in message ... 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? "Robin Hammond" wrote: 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" wrote in message ... 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? |
Progress Bar
"John" wrote in message ... Bob - I am getting a few errors in your code (which very well could be my fault)... Probably mine. I didn't test it as I don'y have that app, just made sure it compiled okay. I think I may just use the usercancelled on the cancel button. If you so desire, feel free to keep posting... I will check on monday. You probably jumped in at the deep end a bit, but it has been interesting no? Post you email address (suitably disguised against spammers) in case I do some more work on it. |
Progress Bar
|
| All times are GMT +1. The time now is 07:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com