Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. . |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. . |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. . . |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making the AND function look at only part of a cell entry | Excel Discussion (Misc queries) | |||
Show value from specific column based on location of active cell | Excel Discussion (Misc queries) | |||
Pop up message upon specific cell entry? | Excel Discussion (Misc queries) | |||
Info from one cell making other cells active | Excel Worksheet Functions | |||
Making Cell Entry Mandatory | Excel Discussion (Misc queries) |