Making a specific row number active from a cell entry

[Bilbo Baggs]

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.

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in and try entering a
row number in a2

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$2" Then
If IsNumeric(Target) Then
Application.EnableEvents = False
Rows(Target.Value).EntireRow.Insert
Application.EnableEvents = True
End If
End If
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Bilbo Baggs]

Mike,

Thank you for that prompt response. I was going to wait until the morning
and hope for the best. It worked a treat but because I failed to explain the
whole issue I have created another problem for myself. The instruction was
going to be part of a larger marco that would work on a protected worksheet
that in addition to creating the new row would copy the cell formatting and
formulae from the row above for columns A-N and then clear the entries in
columns A-E of the new row, finishing off by protecting the worksheet again?

I tried including the line 'ActiveSheet.Unprotect' in your set of
instructions before the line where the new row is created but this did not
work. Any advice?

Thank you.

 

[Chip Pearson]

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

 

[Gord Dibben]

Define "did not work"


Gord Dibben MS Excel MVP

 

[Bilbo Baggs]

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?

 

[Bilbo Baggs]

By "did not work" I mean that entering a number in the cell A2 had no impact

 

[Gord Dibben]

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

 

[Bilbo Baggs]

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

 

[Chip Pearson]

You can use either my code or Mike's code. They both do essentially
the same thing. You can't use both, though. In a module, all procedure
names must be unique. If you pasted both my code and Mike's code (both
named Worksheet_Change) into the same module, you'll get an ambiguous
name error. The code must be placed in the sheet's code module, not a
regular code module. If the sheet is protected, use Me.Unprotect
before inserting the row and Me.Protect after inserting the row.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Bilbo Baggs]

Hi Chip,

That worked thanks. I still have a problem in that once I have created the
new row I need to copy the formulae and formatting details from the row above
into columns F through to N in the new row.

The only way that I can see myself able to achieve this on my own was, I
thought, to record a macro to do the task and then take the code from it and
place it into your code just below the line where the new row is created, but
alas not. Are they not both written in the same programming language - visual
basic?

 

[Chip Pearson]

Try


Private Sub Worksheet_Change(ByVal Target As Range)
Dim RR 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
Set RR = Me.Rows(Target.Value - 1) _
.EntireRow.Cells(1, "F").Resize(2, 9)
RR.FillDown
End If
End If
End If
Application.EnableEvents = True
End If
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Bilbo Baggs]

Chip, That worked thank you. I even managed to introduce the Me.Protect and
Me.Unprotect into the code at the right place to get it to work on a
protected worksheet. Quite pleased with myself. I feel a bit guilty keeping
on but I have now tried to finish the project be being able to also delete a
line by reference to a value in another cell (H3). I've managed to do this by
changing the 'Insert' to 'Delete' but what I can't do is get the two bits of
code to be recognised. It only 'sees' the first bit of code. I've even
renamed the second bit to give it a unique name but no luck. I do appreciate
that both sets of instructions should best be included within the same chunk
of code but I will need to invest alot more time learning all this. How would
you recommend I start out? Also, can you just sort out this last issue for me
- I really appreciate your time.




Private Sub Worksheet_Change(ByVal Target As Range)
Dim RR As Range
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Not Application.Intersect(Range("H3"), 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.Unprotect
Me.Rows(Target.Value).Delete
End If
End If
End If
Application.EnableEvents = True
Me.Protect
End If
End Sub


Private Sub Worksheet_AnotherChange(ByVal Target As Range)
Dim RR As Range
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Not Application.Intersect(Range("C3"), 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.Unprotect
Me.Rows(Target.Value).Insert
Set RR = Me.Rows(Target.Value - 1) _
..EntireRow.Cells(1, "F").Resize(2, 9)
RR.FillDown
End If
End If
End If
Application.EnableEvents = True
Me.Protect
End If
End Sub

 

[Chip Pearson]

There can be only one Change event procedure in the sheet's module.
You can't just make up an event and assume it will be noticed by VBA.
Only events that are defined within the Excel object model can be
automatically executed. In a single Worksheet_Change event procedure,
do something like

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then
Exit Sub
End If

If Target.Address = "$C$3" Then
'
' your code to insert a row
'
ElseIf Target.Address = "$H$3" Then
'
' your code to delete a row
'
End If
End Sub


Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Bilbo Baggs]

Chip,

I have tried to follow your advice but the code, reproduced below, is not
working. I have made an attempt at the 'ElseIf' command but its obviously
woefully inadequate. It won't delete a row with reference to the value in
cell H3? Also, when a new row is created and you insert a number into one of
the new cells within that newly created row it then deletes a row in the
worksheet with reference to that number. Eg. enter 12 into cell C3 and a new
row 12 is created. Go into cell B12 and enter the number 25 and row 25 is
deleted? I only want to delete a row with reference to what is in cell H3?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RR As Range
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Not Application.Intersect(Range("$C$3"), 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.Unprotect
Me.Rows(Target.Value).Insert
Set RR = Me.Rows(Target.Value - 1) _
..EntireRow.Cells(1, "F").Resize(2, 9)
RR.FillDown
End If
End If
End If
Application.EnableEvents = True
Me.Protect
ElseIf Application.Intersect(Range("$H$3"), 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.Unprotect
Me.Rows(Target.Value).Delete
End If
End If
End If
Application.EnableEvents = True
Me.Protect
End If
End Sub

 

[Chip Pearson]

First of all, learn how to properly indent code. Your code is almost
unreadable. Next, you have

ElseIf Application.Intersect(Range("$H$3"), Target) Is Nothing Then

You are missing the NOT. It should be

ElseIf Not Application.Intersect(Range("$H$3"), Target) Is Nothing
Then

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com