Inserting Rows during a SheetChange event

I need to manipulate an excel spreadsheet so that whenever the user
types in something in cell A9, cells B9 and C9 are populated with
predefined data. A new blank row 9 then needs to be inserted, ready
for the next piece of data.

I am using the following code in the SheetChange event:

If (Target.Column = 1) And (Target.Row = 9) Then
Cells.Item(9, 2) = "A N Other" 'insert name
Cells.Item(9, 3) = "PostNo" 'insert Post
Range("A9").Select
Selection.EntireRow.Insert
End If


The name and post get inserted but the blank line does not get
inserted. Is this because I am changing the sheet during a
ChangeSheet event?

How can I get around this?

Nirmal

Hi Nirmal,
Something like this should work, you will have to change the text to equal
what you are exactly looking for:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A9").Value = "A N Other" And Range("B9").Value = "2nd Other" And _
Range("C9").Value = "Other Data" Then
Selection.EntireRow.Insert
End If
End Sub

"Nirmal Singh" wrote:

>
> I need to manipulate an excel spreadsheet so that whenever the user
> types in something in cell A9, cells B9 and C9 are populated with
> predefined data. A new blank row 9 then needs to be inserted, ready
> for the next piece of data.
>
> I am using the following code in the SheetChange event:
>
> If (Target.Column = 1) And (Target.Row = 9) Then
> Cells.Item(9, 2) = "A N Other" 'insert name
> Cells.Item(9, 3) = "PostNo" 'insert Post
> Range("A9").Select
> Selection.EntireRow.Insert
> End If
>
>
> The name and post get inserted but the blank line does not get
> inserted. Is this because I am changing the sheet during a
> ChangeSheet event?
>
> How can I get around this?
>
> Nirmal
>
>

try this
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.row <> 9 Or Target.Column <> 1 Then Exit Sub
With Target
..Offset(0, 1) = "a"
..Offset(0, 2) = "b"
..EntireRow.Insert
End With
End Sub

--
Don Guillett
SalesAid Software
[email protected]
"Nirmal Singh" <[email protected]> wrote in
message news:[email protected]...
>
> I need to manipulate an excel spreadsheet so that whenever the user
> types in something in cell A9, cells B9 and C9 are populated with
> predefined data. A new blank row 9 then needs to be inserted, ready
> for the next piece of data.
>
> I am using the following code in the SheetChange event:
>
> If (Target.Column = 1) And (Target.Row = 9) Then
> Cells.Item(9, 2) = "A N Other" 'insert name
> Cells.Item(9, 3) = "PostNo" 'insert Post
> Range("A9").Select
> Selection.EntireRow.Insert
> End If
>
>
> The name and post get inserted but the blank line does not get
> inserted. Is this because I am changing the sheet during a
> ChangeSheet event?
>
> How can I get around this?
>
> Nirmal
>

On Thu, 6 Jan 2005 13:12:50 -0600, "Don Guillett" <[email protected]>
wrote:

>try this
>Private Sub Worksheet_Change(ByVal Target As Range)
>If Target.row <> 9 Or Target.Column <> 1 Then Exit Sub
>With Target
>.Offset(0, 1) = "a"
>.Offset(0, 2) = "b"
>.EntireRow.Insert
>End With
>End Sub
>

Thanks Don, that is exactly what I needed.

Nirmal

glad to help

--
Don Guillett
SalesAid Software
[email protected]
"Nirmal Singh" <[email protected]> wrote in
message news:[email protected]...
> On Thu, 6 Jan 2005 13:12:50 -0600, "Don Guillett" <[email protected]>
> wrote:
>
> >try this
> >Private Sub Worksheet_Change(ByVal Target As Range)
> >If Target.row <> 9 Or Target.Column <> 1 Then Exit Sub
> >With Target
> >.Offset(0, 1) = "a"
> >.Offset(0, 2) = "b"
> >.EntireRow.Insert
> >End With
> >End Sub
> >
>
> Thanks Don, that is exactly what I needed.
>
> Nirmal
>