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

Inserting Rows during a SheetChange event
 

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

Inserting Rows during a SheetChange event
 

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

"Nirmal Singh" wrote in
message ...

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

Inserting Rows during a SheetChange event
 

On Thu, 6 Jan 2005 13:12:50 -0600, "Don Guillett"
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

Inserting Rows during a SheetChange event
 

glad to help

--
Don Guillett
SalesAid Software

"Nirmal Singh" wrote in
message ...
On Thu, 6 Jan 2005 13:12:50 -0600, "Don Guillett"
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