Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button Request
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button Request
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. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Saxman" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button Request
Chip Pearson wrote:
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
command button add another command | Excel Discussion (Misc queries) | |||
VB's Command Button vs Form's Command Button | Excel Programming | |||
Command Button vs Form Button | Excel Programming | |||
Command Button vs Control Button | Excel Programming | |||
Command Button vs Form Button | Excel Programming |