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

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

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

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

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

Halt Macro Execution
 

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

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

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

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

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