Have you pasted either set of code into the worksheet module?
What is the value in A2?
How do you know a row is not being inserted?
Did you want the unprotect and protect statements?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then
Exit Sub
End If
If Not Application.Intersect(Range("A2"), Target) Is Nothing Then
On Error GoTo endit
Application.EnableEvents = False
If Target.HasFormula = False Then
If IsNumeric(Target.Value) Then
If Target.Value 0 And _
Target.Value < Me.Rows.Count Then
ActiveSheet.Unprotect Password:="justme"
Me.Rows(Target.Value).Insert
End If
End If
End If
endit:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme"
End If
End Sub
Gord Dibben MS Excel MVP
On Thu, 20 May 2010 11:42:06 -0700, Bilbo Baggs
wrote:
Hi Chip,
Not sure as to whether I am supposed to be pasting this as a replacement for
Mike's code or before or after his code; either way it does not appear to
have any affect on the spreadsheet?
"Chip Pearson" wrote:
Right click on the sheet tab in questions and choose View Code from
the popup menu. That will open the code module for that worksheet.
There, paste the following:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then
Exit Sub
End If
If Not Application.Intersect(Range("A2"), Target) Is Nothing Then
Application.EnableEvents = False
If Target.HasFormula = False Then
If IsNumeric(Target.Value) Then
If Target.Value 0 And _
Target.Value < Me.Rows.Count Then
Me.Rows(Target.Value).Insert
End If
End If
End If
Application.EnableEvents = True
End If
End Sub
Change the reference to "A2" to the cell whose value controls which
row to insert.
Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
On Thu, 20 May 2010 08:29:01 -0700, Bilbo Baggs <Bilbo
wrote:
Hi,
I need to be able to insert a new row into a worksheet at row 'x' where 'x'
is determined by the value in a particular cell. So e.g. if the user enters
the number '34' into cell A2 I want to be able to wite a short macro that
will when run insert a new row into the worksheet at row 34. I just cannot
find a way to automate the process of selecting the require row number?
Strugling with this for hours - would appreciate any help. This is my first
ever online question. Hope it makes sense.
Thank you.
.