Hi
I'm more of an Access user than Excel but have been asked to produce a worksheet that does certain tasks and have become a little stuck with some of the functionality.
There are two things that I can not fathom out how to perform, and wondered if anyone could point me in the right direction.
1. I am using code to insert a new row and copy formula (code courtesy of http://www.mvps.org/dmcritchie/excel/insrtrow.htm). I would like to attach the code to a particular cell, so that it always runs from that cell. In the example below, you will see in cell C11 the text "Insert New Row". I would like to ensure that the code runs when this text is clicked. This would ensure that the user always adds the new row to the end of the category (i.e. the row above where the "Insert New Row" text appears). Obviously, if a new row is added, the text will move down, so I can't just reference the current cell. Is there any kind of OnClick of Cell event?
2. Currently, M6:M10 has a named range defined, which is then refered to in M11. If I insert a new row at the bottom of this category, this row is not included in the named range. Is it possible to update the named range to include the new row?
Thanks in advance for any advice or pointers.
Graham
I'm more of an Access user than Excel but have been asked to produce a worksheet that does certain tasks and have become a little stuck with some of the functionality.
There are two things that I can not fathom out how to perform, and wondered if anyone could point me in the right direction.
1. I am using code to insert a new row and copy formula (code courtesy of http://www.mvps.org/dmcritchie/excel/insrtrow.htm). I would like to attach the code to a particular cell, so that it always runs from that cell. In the example below, you will see in cell C11 the text "Insert New Row". I would like to ensure that the code runs when this text is clicked. This would ensure that the user always adds the new row to the end of the category (i.e. the row above where the "Insert New Row" text appears). Obviously, if a new row is added, the text will move down, so I can't just reference the current cell. Is there any kind of OnClick of Cell event?
2. Currently, M6:M10 has a named range defined, which is then refered to in M11. If I insert a new row at the bottom of this category, this row is not included in the named range. Is it possible to update the named range to include the new row?
| Test_Ashleigh.xls | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| C | D | E | F | G | H | I | J | K | L | M | |||
| 3 | Category | ||||||||||||
| 4 | Quantity | PricePerPerson | Total | ||||||||||
| 5 | Food: | ||||||||||||
| 6 | TestFoodItemNo.1 | 4 | 10.00 | 40.00 | |||||||||
| 7 | TestFoodItemNo.2 | 1 | 15.00 | 15.00 | |||||||||
| 8 | TestFoodItemNo.3 | ||||||||||||
| 9 | TestFoodItemNo.4 | ||||||||||||
| 10 | TestFoodItemNo.5 | 1 | 1.00 | 1.00 | |||||||||
| 11 | InsertNewRow | Food:SubTotal | 56.00 | ||||||||||
| 12 | Miscellaneous: | ||||||||||||
| 13 | TestMiscellaneousItemNo.1 | 2 | 10.00 | 20.00 | |||||||||
| 14 | TestMiscellaneousItemNo.2 | ||||||||||||
| 15 | TestMiscellaneousItemNo.3 | ||||||||||||
| 16 | TestMiscellaneousItemNo.4 | ||||||||||||
| 17 | TestMiscellaneousItemNo.5 | ||||||||||||
| 18 | InsertNewRow | Miscellaneous:SubTotal | 20.00 | ||||||||||
Sheet1 | |||||||||||||
Thanks in advance for any advice or pointers.
Graham
