Making a specific row number active from a cell entry
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. |
Making a specific row number active from a cell entry
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" 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. |
Making a specific row number active from a cell entry
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. "Mike H" wrote: 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" 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. |
Making a specific row number active from a cell entry
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. |
Making a specific row number active from a cell entry
Define "did not work"
Gord Dibben MS Excel MVP On Thu, 20 May 2010 09:23:01 -0700, Bilbo Baggs wrote: 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. "Mike H" wrote: 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" 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. |
Making a specific row number active from a cell entry
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. . |
Making a specific row number active from a cell entry
By "did not work" I mean that entering a number in the cell A2 had no impact
on the worksheet" - it failed to insert a new row. "Gord Dibben" wrote: Define "did not work" Gord Dibben MS Excel MVP On Thu, 20 May 2010 09:23:01 -0700, Bilbo Baggs wrote: 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. "Mike H" wrote: 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" 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. . |
Making a specific row number active from a cell entry
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. . |
Making a specific row number active from a cell entry
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. . . |
Making a specific row number active from a cell entry
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. . . |
Making a specific row number active from a cell entry
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. . . . |
Making a specific row number active from a cell entry
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. . . . |
Making a specific row number active from a cell entry
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. . . . . |
Making a specific row number active from a cell entry
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. . . . . |
Making a specific row number active from a cell entry
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" wrote: 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. . |
Making a specific row number active from a cell entry
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 On Mon, 24 May 2010 08:46:04 -0700, Bilbo Baggs wrote: 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" wrote: 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. . |
All times are GMT +1. The time now is 09:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com