Inserting Rows during a SheetChange event

[Nirmal Singh]

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

 

[David]

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

 

[Don Guillett]

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

 

[Nirmal Singh]

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

 

[Don Guillett]

glad to help