Gord,

I have been somewhat confused. My initial query was resolved by Mike and I
then went back to him as I wanted to be able to achieve the same result but
with a protected worksheet. Shortly afterwards Chip gave me some code that I
thought was going to resolve the problem but I've since discovered that
infact he was offering me a solution to my original problem. The code looked
quite different, which is why I thought he was answering my second point
about solving the issue on a protected worksheet, but infact he wasn't.

You've now come on board to help - thank you for that. The value in cell A2
(an unprotected cell is simply a number say '37' I know it's not including a
fresh new row as all the rows have data in them and a new row would
presemably be blank.

I have tried pasting your code into what I assume is the worksheet module
(it's where I pasted the Mike's first code) and when I enter a number in cell
A2 it tells me that I have a complier error "ambiguous name detected:
Worksheet_ change.

I am new to all this stuff (Visual Basic, I believe) YOur code is now below
Mikes and separated by a horizontal line. I have no idea if you can join code
like this together.?



"Gord Dibben" wrote:

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.
.


.