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?

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?

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?

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?

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

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

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?

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?

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?

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?

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?