I'm trying to write a simple macro in VB to insert a line into a spreadsheet
that I have been developing. The spreadsheet is a for Project Managers to
note snagging items in refurbishment projects. The sheet has a number of
formulas on each row. I would like to create a command button that allows the
user to select a row and insert a line at the activecell (copying and pasting
a clear line that I have hidden at the bottom of the sheet).

My problem is that I don't know how to prompt/allow the user to select a
line. With the current code (a simplified version below) the macro inserts
the line below the line I am copying (because its the activecell).

Rows("9:9").Select
Selection.Copy
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown

Could anyone tell me the code that allows me to break the macro, allow the
user to select a cell/row and the restart the macro.

Any help would be much appreciated.

Phil

Unfortunately, you can't "break into" macro for user interaction. The
method I use when I have to allow the user to interact is to create two
macros: the first macro runs until user interaction is required, then pops
up a modeless UserForm with a button to continue the process. This allows
the user to do whatever, then click the button to run the second macro,
which completes the whole thing.

Ed

"PW11111" wrote in message
...
I'm trying to write a simple macro in VB to insert a line into a
spreadsheet
that I have been developing. The spreadsheet is a for Project Managers to
note snagging items in refurbishment projects. The sheet has a number of
formulas on each row. I would like to create a command button that allows
the
user to select a row and insert a line at the activecell (copying and
pasting
a clear line that I have hidden at the bottom of the sheet).

My problem is that I don't know how to prompt/allow the user to select a
line. With the current code (a simplified version below) the macro inserts
the line below the line I am copying (because its the activecell).

Rows("9:9").Select
Selection.Copy
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown

Could anyone tell me the code that allows me to break the macro, allow the
user to select a cell/row and the restart the macro.

Any help would be much appreciated.

Phil

Hi
If you write
rows("9:9").copy
instead of selecting range then your activecell for pasting will be where
ever you start the macro from
or you could use this
Application.Goto Reference:=InputBox("PLEASE TYPE R PLUS ROW NUMBER REQUIRED
EG R6", Title:="WHAT")
After selection.copy
Hope this helps
Tina
Tina

"PW11111" wrote:

I'm trying to write a simple macro in VB to insert a line into a spreadsheet
that I have been developing. The spreadsheet is a for Project Managers to
note snagging items in refurbishment projects. The sheet has a number of
formulas on each row. I would like to create a command button that allows the
user to select a row and insert a line at the activecell (copying and pasting
a clear line that I have hidden at the bottom of the sheet).

My problem is that I don't know how to prompt/allow the user to select a
line. With the current code (a simplified version below) the macro inserts
the line below the line I am copying (because its the activecell).

Rows("9:9").Select
Selection.Copy
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown

Could anyone tell me the code that allows me to break the macro, allow the
user to select a cell/row and the restart the macro.

Any help would be much appreciated.

Phil

Sub InsertRow()
Dim rng As Range
On Error Resume Next
Set rng = Application.InputBox("Select location to insert row; use mouse to
select", Type:=8)
On Error GoTo 0
If Not rng Is Nothing Then
Rows(9).Copy
rng.EntireRow.Insert xlShiftDown
End If

--
Regards,
Tom Ogilvy


"PW11111" wrote in message
...
I'm trying to write a simple macro in VB to insert a line into a
spreadsheet
that I have been developing. The spreadsheet is a for Project Managers to
note snagging items in refurbishment projects. The sheet has a number of
formulas on each row. I would like to create a command button that allows
the
user to select a row and insert a line at the activecell (copying and
pasting
a clear line that I have hidden at the bottom of the sheet).

My problem is that I don't know how to prompt/allow the user to select a
line. With the current code (a simplified version below) the macro inserts
the line below the line I am copying (because its the activecell).

Rows("9:9").Select
Selection.Copy
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown

Could anyone tell me the code that allows me to break the macro, allow the
user to select a cell/row and the restart the macro.

Any help would be much appreciated.

Phil