Command Button Request

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Saxman - 18 Jan 2007 12:12 GMT

Sorry, but I did not realise there was a live thread with the same title
'Command Button' which is where my original request ended.

I would like to create a dual task command button that would stop some
code running and clear data on another worksheet. Or would it be best
to keep these operations separate?

Clearing the data could be done with a macro assigned to the command
button, but I'm not sure how I would go about stopping the code running.

Any help appreciated thanks.

Reply to this Message

Chip Pearson - 18 Jan 2007 12:31 GMT

The only way I can think of to terminate running code is to modify the loop
(I assume the running code is in some sort of Loop structure) to test the
value of a Public variable, whose value would be set by your command button.
For example, declare a Public variable named Done as a Boolean in the
module containing the loop code and write your command button code as

Sub CommmandButton1_Click()
Done = True
' clear the ranges
End Sub

Then in the module containing the procedure that needs to be told to quit,
declare a variable as a Public variable (outside of and before any procedure
in the module):

Public Done As Boolean

Then test this variable in your loop code. E.g,

Public Sub YourSub()
Done = False
Do Until (your_normal_terminate_condition) Or (Done = True)
'''''''''''''''''''''''''''''''''
' your code here
'''''''''''''''''''''''''''''''''
Loop
''''''''''''''''''''''''''''''''''''''''
' cleanup code as required
'''''''''''''''''''''''''''''''''''''''
End Sub

This assumes that the Button is from the Controls toolbar. If it is from the
Forms tool bar, declare the variable as described above, and just enter
Done = True
to the macro that is already assigned to the button. Note that in both
cases, the declaration of the variable Done must be Public, outside of and
before any procedure in the module. If you declare the variable within a
procedure, it won't work.

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

> Sorry, but I did not realise there was a live thread with the same title
> 'Command Button' which is where my original request ended.
[quoted text clipped - 7 lines]
>
> Any help appreciated thanks.

Reply to this Message

Saxman - 18 Jan 2007 16:49 GMT

> The only way I can think of to terminate running code is to modify the loop
> (I assume the running code is in some sort of Loop structure) to test the
> value of a Public variable, whose value would be set by your command button.
> For example, declare a Public variable named Done as a Boolean in the
> module containing the loop code and write your command button code as

Thanks for the feedback. Looking at the code below, there is already a
request to clear the data from the 'archive' worksheet, so a dual
function button will not be necessary, just a stop button for the loop.

This is the code below, but I am not sure where to enter your code as
I'm a novice at these things.

............................................................................

Private Sub CommandButton1_Click()
Application.EnableEvents = False
PauseTime = 30 'Pause (in seconds)
'This bit clears the Archive sheet and formats cells to numbers
with 2dps
Sheets("Archive").Select
Sheets("Archive").Cells.Select
Selection.ClearContents
Selection.NumberFormat = "0.00"
Sheets("Data").Select
Sheets("Data").Cells(20, 6) = 0 'Record number of copies
'Start by pasting the horsenames
'Only do this once so outside the loop
Worksheets("Data").Range("A5:A19").Copy
Worksheets("Archive").Select
Worksheets("Archive").Cells(1, 1).Select 'Paste the results in
the top row in Archive
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True 'Paste values not
formula and transpose
Sheets("Data").Select 'Switch view back to
Data sheet
'Start loop to transfer prices every 30 secs.
Do
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Sheets("Data").Cells(3, 1) = Int(Timer - Start) 'Display
secs since last copy on sheet
Loop
Worksheets("Data").Range("F5:F19").Copy
reqRow = Sheets("Data").Cells(20, 6) + 2 'Paste the results
in the next row in Archive
Worksheets("Archive").Select
Worksheets("Archive").Cells(reqRow, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True 'Paste values not
formula
Sheets("Data").Select 'Switch view back to
Data sheet
Sheets("Data").Cells(20, 6) = Sheets("Data").Cells(20, 6) + 1
'Count number of copies
Loop Until Sheets("Data").Cells(20, 6) = 10 'Alter this for number
of copies
Sheets("Data").Cells(3, 1) = "Finished"
Application.EnableEvents = True
End Sub

Reply to this Message