| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Halt Macro Execution
I need to halt a macro ( a series of sub routines) to post instructions and
allow user input. I have been trying a User Form to display the instructions so that closing the form would continue the macro. The problem is that once the form is shown it does not allow editing of the worksheet. Any help would be appreciated. Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Halt Macro Execution
To allow physical/manual direct editing of the worksheet, you pretty much
need the macro to end. Obviously the workaround it to build some type of interface where the user provides input to the interface and your code changes the sheet. -- Regards, Tom Ogilvy "PeteN" wrote: I need to halt a macro ( a series of sub routines) to post instructions and allow user input. I have been trying a User Form to display the instructions so that closing the form would continue the macro. The problem is that once the form is shown it does not allow editing of the worksheet. Any help would be appreciated. Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Halt Macro Execution
look in the vba help index for
messagebox or inputbox -- Don Guillett Microsoft MVP Excel SalesAid Software "PeteN" wrote in message ... I need to halt a macro ( a series of sub routines) to post instructions and allow user input. I have been trying a User Form to display the instructions so that closing the form would continue the macro. The problem is that once the form is shown it does not allow editing of the worksheet. Any help would be appreciated. Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Halt Macro Execution
Tom,
Halting the macro was a 'get out' because I could not come up with a solution that did not require user intervention. I have a worksheet of which two columns are 'PartNumber' and 'Qty' as below PartNumber Qty 23456-444 1 24477-079-02 4 24477-079-03 2 24536-900 1 26655-234-05 3 26655-234-07 1 26756-654 2 etc What I need to do is where the PartNumber has a suffix (02, 03 etc) I need to increase the Qty by multiplying it by the suffix (4 x 2=8) and then removing the suffix (-02). I then need to total all like part numbers with the total qty being in the first occurance of that number. Then delete all other rows of that number. The sheet would then look like this: PartNumber Qty 23456-444 1 24477-079-02 14 24536-900 1 26655-234-05 22 26756-654 2 You can see why I went for the 'easier' solution! "Tom Ogilvy" wrote: To allow physical/manual direct editing of the worksheet, you pretty much need the macro to end. Obviously the workaround it to build some type of interface where the user provides input to the interface and your code changes the sheet. -- Regards, Tom Ogilvy "PeteN" wrote: I need to halt a macro ( a series of sub routines) to post instructions and allow user input. I have been trying a User Form to display the instructions so that closing the form would continue the macro. The problem is that once the form is shown it does not allow editing of the worksheet. Any help would be appreciated. Thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Halt Macro Execution
Tom,
Sorry an error, the 'after' should look like this; PartNumber Qty 23456-444 1 24477-079 14 24536-900 1 26655-234 22 26756-654 2 "PeteN" wrote: Tom, Halting the macro was a 'get out' because I could not come up with a solution that did not require user intervention. I have a worksheet of which two columns are 'PartNumber' and 'Qty' as below PartNumber Qty 23456-444 1 24477-079-02 4 24477-079-03 2 24536-900 1 26655-234-05 3 26655-234-07 1 26756-654 2 etc What I need to do is where the PartNumber has a suffix (02, 03 etc) I need to increase the Qty by multiplying it by the suffix (4 x 2=8) and then removing the suffix (-02). I then need to total all like part numbers with the total qty being in the first occurance of that number. Then delete all other rows of that number. The sheet would then look like this: PartNumber Qty 23456-444 1 24477-079-02 14 24536-900 1 26655-234-05 22 26756-654 2 You can see why I went for the 'easier' solution! "Tom Ogilvy" wrote: To allow physical/manual direct editing of the worksheet, you pretty much need the macro to end. Obviously the workaround it to build some type of interface where the user provides input to the interface and your code changes the sheet. -- Regards, Tom Ogilvy "PeteN" wrote: I need to halt a macro ( a series of sub routines) to post instructions and allow user input. I have been trying a User Form to display the instructions so that closing the form would continue the macro. The problem is that once the form is shown it does not allow editing of the worksheet. Any help would be appreciated. Thanks |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Halt Macro Execution
Don,
I got this to run by changing "B" to "A" (part numbers are in column A). It works from row 8 upwards, this is a problem as there could be 100+ rows. It stopped with a 'type mismatch' on the line "Cells(i, 3) = Mid(Cells(i, 2), 11, 2) * Cells(i, 3)". "Don Guillett" wrote: Try this for the data presented where part number in column B. Assumes sorted and all the same length. Sub FixPartNums() For i = 8 To 2 Step -1 If InStr(7, Cells(i, "B"), "-") 1 Then Cells(i, 3) = Mid(Cells(i, 2), 11, 2) * Cells(i, 3) Cells(i, 2) = Left(Cells(i, 2), 9) If Cells(i, 2) = Cells(i + 1, 2) Then Cells(i, 3) = Cells(i + 1, 3) + Cells(i, 3) Rows(i + 1).Delete End If End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "PeteN" wrote in message ... Tom, Sorry an error, the 'after' should look like this; PartNumber Qty 23456-444 1 24477-079 14 24536-900 1 26655-234 22 26756-654 2 "PeteN" wrote: Tom, Halting the macro was a 'get out' because I could not come up with a solution that did not require user intervention. I have a worksheet of which two columns are 'PartNumber' and 'Qty' as below PartNumber Qty 23456-444 1 24477-079-02 4 24477-079-03 2 24536-900 1 26655-234-05 3 26655-234-07 1 26756-654 2 etc What I need to do is where the PartNumber has a suffix (02, 03 etc) I need to increase the Qty by multiplying it by the suffix (4 x 2=8) and then removing the suffix (-02). I then need to total all like part numbers with the total qty being in the first occurance of that number. Then delete all other rows of that number. The sheet would then look like this: PartNumber Qty 23456-444 1 24477-079-02 14 24536-900 1 26655-234-05 22 26756-654 2 You can see why I went for the 'easier' solution! "Tom Ogilvy" wrote: To allow physical/manual direct editing of the worksheet, you pretty much need the macro to end. Obviously the workaround it to build some type of interface where the user provides input to the interface and your code changes the sheet. -- Regards, Tom Ogilvy "PeteN" wrote: I need to halt a macro ( a series of sub routines) to post instructions and allow user input. I have been trying a User Form to display the instructions so that closing the form would continue the macro. The problem is that once the form is shown it does not allow editing of the worksheet. Any help would be appreciated. Thanks |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Halt Macro Execution
You will need to change all references to use for col A. ie:
cells(i,2) to cells(i,1) etc and change the for 8 to 2 step -1 to 100 to 2 step -1 -- Don Guillett Microsoft MVP Excel SalesAid Software "PeteN" wrote in message ... Don, I got this to run by changing "B" to "A" (part numbers are in column A). It works from row 8 upwards, this is a problem as there could be 100+ rows. It stopped with a 'type mismatch' on the line "Cells(i, 3) = Mid(Cells(i, 2), 11, 2) * Cells(i, 3)". "Don Guillett" wrote: Try this for the data presented where part number in column B. Assumes sorted and all the same length. Sub FixPartNums() For i = 8 To 2 Step -1 If InStr(7, Cells(i, "B"), "-") 1 Then Cells(i, 3) = Mid(Cells(i, 2), 11, 2) * Cells(i, 3) Cells(i, 2) = Left(Cells(i, 2), 9) If Cells(i, 2) = Cells(i + 1, 2) Then Cells(i, 3) = Cells(i + 1, 3) + Cells(i, 3) Rows(i + 1).Delete End If End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "PeteN" wrote in message ... Tom, Sorry an error, the 'after' should look like this; PartNumber Qty 23456-444 1 24477-079 14 24536-900 1 26655-234 22 26756-654 2 "PeteN" wrote: Tom, Halting the macro was a 'get out' because I could not come up with a solution that did not require user intervention. I have a worksheet of which two columns are 'PartNumber' and 'Qty' as below PartNumber Qty 23456-444 1 24477-079-02 4 24477-079-03 2 24536-900 1 26655-234-05 3 26655-234-07 1 26756-654 2 etc What I need to do is where the PartNumber has a suffix (02, 03 etc) I need to increase the Qty by multiplying it by the suffix (4 x 2=8) and then removing the suffix (-02). I then need to total all like part numbers with the total qty being in the first occurance of that number. Then delete all other rows of that number. The sheet would then look like this: PartNumber Qty 23456-444 1 24477-079-02 14 24536-900 1 26655-234-05 22 26756-654 2 You can see why I went for the 'easier' solution! "Tom Ogilvy" wrote: To allow physical/manual direct editing of the worksheet, you pretty much need the macro to end. Obviously the workaround it to build some type of interface where the user provides input to the interface and your code changes the sheet. -- Regards, Tom Ogilvy "PeteN" wrote: I need to halt a macro ( a series of sub routines) to post instructions and allow user input. I have been trying a User Form to display the instructions so that closing the form would continue the macro. The problem is that once the form is shown it does not allow editing of the worksheet. Any help would be appreciated. Thanks |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Halt Macro Execution
Thanks Don, it works well.
"Don Guillett" wrote: You will need to change all references to use for col A. ie: cells(i,2) to cells(i,1) etc and change the for 8 to 2 step -1 to 100 to 2 step -1 -- Don Guillett Microsoft MVP Excel SalesAid Software "PeteN" wrote in message ... Don, I got this to run by changing "B" to "A" (part numbers are in column A). It works from row 8 upwards, this is a problem as there could be 100+ rows. It stopped with a 'type mismatch' on the line "Cells(i, 3) = Mid(Cells(i, 2), 11, 2) * Cells(i, 3)". "Don Guillett" wrote: Try this for the data presented where part number in column B. Assumes sorted and all the same length. Sub FixPartNums() For i = 8 To 2 Step -1 If InStr(7, Cells(i, "B"), "-") 1 Then Cells(i, 3) = Mid(Cells(i, 2), 11, 2) * Cells(i, 3) Cells(i, 2) = Left(Cells(i, 2), 9) If Cells(i, 2) = Cells(i + 1, 2) Then Cells(i, 3) = Cells(i + 1, 3) + Cells(i, 3) Rows(i + 1).Delete End If End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "PeteN" wrote in message ... Tom, Sorry an error, the 'after' should look like this; PartNumber Qty 23456-444 1 24477-079 14 24536-900 1 26655-234 22 26756-654 2 "PeteN" wrote: Tom, Halting the macro was a 'get out' because I could not come up with a solution that did not require user intervention. I have a worksheet of which two columns are 'PartNumber' and 'Qty' as below PartNumber Qty 23456-444 1 24477-079-02 4 24477-079-03 2 24536-900 1 26655-234-05 3 26655-234-07 1 26756-654 2 etc What I need to do is where the PartNumber has a suffix (02, 03 etc) I need to increase the Qty by multiplying it by the suffix (4 x 2=8) and then removing the suffix (-02). I then need to total all like part numbers with the total qty being in the first occurance of that number. Then delete all other rows of that number. The sheet would then look like this: PartNumber Qty 23456-444 1 24477-079-02 14 24536-900 1 26655-234-05 22 26756-654 2 You can see why I went for the 'easier' solution! "Tom Ogilvy" wrote: To allow physical/manual direct editing of the worksheet, you pretty much need the macro to end. Obviously the workaround it to build some type of interface where the user provides input to the interface and your code changes the sheet. -- Regards, Tom Ogilvy "PeteN" wrote: I need to halt a macro ( a series of sub routines) to post instructions and allow user input. I have been trying a User Form to display the instructions so that closing the form would continue the macro. The problem is that once the form is shown it does not allow editing of the worksheet. Any help would be appreciated. Thanks |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Halt Macro Execution
Easier than doing each by hand. Glad to help. -- Don Guillett Microsoft MVP Excel SalesAid Software "PeteN" wrote in message ... Thanks Don, it works well. "Don Guillett" wrote: You will need to change all references to use for col A. ie: cells(i,2) to cells(i,1) etc and change the for 8 to 2 step -1 to 100 to 2 step -1 -- Don Guillett Microsoft MVP Excel SalesAid Software "PeteN" wrote in message ... Don, I got this to run by changing "B" to "A" (part numbers are in column A). It works from row 8 upwards, this is a problem as there could be 100+ rows. It stopped with a 'type mismatch' on the line "Cells(i, 3) = Mid(Cells(i, 2), 11, 2) * Cells(i, 3)". "Don Guillett" wrote: Try this for the data presented where part number in column B. Assumes sorted and all the same length. Sub FixPartNums() For i = 8 To 2 Step -1 If InStr(7, Cells(i, "B"), "-") 1 Then Cells(i, 3) = Mid(Cells(i, 2), 11, 2) * Cells(i, 3) Cells(i, 2) = Left(Cells(i, 2), 9) If Cells(i, 2) = Cells(i + 1, 2) Then Cells(i, 3) = Cells(i + 1, 3) + Cells(i, 3) Rows(i + 1).Delete End If End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "PeteN" wrote in message ... Tom, Sorry an error, the 'after' should look like this; PartNumber Qty 23456-444 1 24477-079 14 24536-900 1 26655-234 22 26756-654 2 "PeteN" wrote: Tom, Halting the macro was a 'get out' because I could not come up with a solution that did not require user intervention. I have a worksheet of which two columns are 'PartNumber' and 'Qty' as below PartNumber Qty 23456-444 1 24477-079-02 4 24477-079-03 2 24536-900 1 26655-234-05 3 26655-234-07 1 26756-654 2 etc What I need to do is where the PartNumber has a suffix (02, 03 etc) I need to increase the Qty by multiplying it by the suffix (4 x 2=8) and then removing the suffix (-02). I then need to total all like part numbers with the total qty being in the first occurance of that number. Then delete all other rows of that number. The sheet would then look like this: PartNumber Qty 23456-444 1 24477-079-02 14 24536-900 1 26655-234-05 22 26756-654 2 You can see why I went for the 'easier' solution! "Tom Ogilvy" wrote: To allow physical/manual direct editing of the worksheet, you pretty much need the macro to end. Obviously the workaround it to build some type of interface where the user provides input to the interface and your code changes the sheet. -- Regards, Tom Ogilvy "PeteN" wrote: I need to halt a macro ( a series of sub routines) to post instructions and allow user input. I have been trying a User Form to display the instructions so that closing the form would continue the macro. The problem is that once the form is shown it does not allow editing of the worksheet. Any help would be appreciated. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Halt or Pause a Macro | Excel Programming | |||
| Halt A Macro If a Cell Contains a Certain Value | Excel Programming | |||
| Halt all code while macro runs | Excel Programming | |||
| Code to halt a macro | Excel Programming | |||
Linear Mode
