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
On Sat, 22 May 2010 04:23:01 -0700, Bilbo Baggs
wrote:
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" wrote:
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
On Fri, 21 May 2010 02:36:01 -0700, Bilbo Baggs
wrote:
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" wrote:
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
On Thu, 20 May 2010 13:30:01 -0700, Bilbo Baggs
wrote:
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.
.
.
.
.