Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert new row with drop-down box but problem with cell linkreferring to the original drop-down box
I have a sheet called "Action plan", that has a summary in the top and
rows below starting from row 11. It ranges from 11A to AN11. Through these rows I have a number of various drop-down boxes, which pull data from a sheet called "mapping". Im trying to create a button that can insert a new row below the last row (ie. 12) with all its different drop-down boxes. However, Im using cell-link with the drop-down box, which I seem unable to copy as a variable. In example I have a drop-down box in K11 with a cell-link in L11. When I try to copy the whole row manually till row 12, it does not seem to change the cell-link reference to K12, but will still be linked to K11. In my example I have the drop-down box pull data from column C and column D on the "mapping" sheet. Where column C is the drop-down box, and column D gives the cell-link value. Im a newbie at macro's and have tried searching the web for a solution, though I have not found one so far. I hope you will be able to help me. Thank you in advance for your time and help. /Phil |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert new row with drop-down box but problem with cell linkreferring to the original drop-down box
When I try recording a new macro this is what I get:
Sub Makro4() ' ' Makro4 Makro ' ' ActiveSheet.DropDowns.Add(1990.5, 484.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 484.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 484.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 484.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(711.75, 488.25, 86.25, 21).Select ActiveSheet.DropDowns.Add(664.5, 488.25, 34.5, 21).Select ActiveSheet.DropDowns.Add(510, 488.25, 34.5, 21).Select ActiveSheet.DropDowns.Add(569.25, 488.25, 86.25, 21).Select ActiveSheet.DropDowns.Add(967.5, 487.5, 78, 19.5).Select ActiveSheet.DropDowns.Add(453.75, 488.25, 34.5, 21).Select ActiveSheet.DropDowns.Add(920.25, 488.25, 34.5, 21).Select ActiveSheet.DropDowns.Add(1207.5, 487.5, 45.75, 19.5).Select ActiveSheet.DropDowns.Add(1289.25, 487.5, 45.75, 19.5).Select ActiveSheet.DropDowns.Add(1365, 487.5, 45.75, 19.5).Select ActiveSheet.DropDowns.Add(1551, 487.5, 80.25, 19.5).Select ActiveSheet.DropDowns.Add(1707, 487.5, 63, 19.5).Select ActiveSheet.DropDowns.Add(2525.25, 487.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select Selection.AutoFill Destination:=Rows("19:20"), Type:=xlFillDefault Rows("19:20").Select End Sub and If I record a macro where I change the cell link I get: Sub Makro5() ' ' Makro5 Makro ' ' ActiveSheet.DropDowns.Add(1990.5, 484.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 484.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 484.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 484.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(711.75, 488.25, 86.25, 21).Select ActiveSheet.DropDowns.Add(664.5, 488.25, 34.5, 21).Select ActiveSheet.DropDowns.Add(510, 488.25, 34.5, 21).Select ActiveSheet.DropDowns.Add(569.25, 488.25, 86.25, 21).Select ActiveSheet.DropDowns.Add(967.5, 487.5, 78, 19.5).Select ActiveSheet.DropDowns.Add(453.75, 488.25, 34.5, 21).Select ActiveSheet.DropDowns.Add(920.25, 488.25, 34.5, 21).Select ActiveSheet.DropDowns.Add(1207.5, 487.5, 45.75, 19.5).Select ActiveSheet.DropDowns.Add(1289.25, 487.5, 45.75, 19.5).Select ActiveSheet.DropDowns.Add(1365, 487.5, 45.75, 19.5).Select ActiveSheet.DropDowns.Add(1551, 487.5, 80.25, 19.5).Select ActiveSheet.DropDowns.Add(1707, 487.5, 63, 19.5).Select ActiveSheet.DropDowns.Add(2525.25, 487.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select Selection.AutoFill Destination:=Rows("19:20"), Type:=xlFillDefault Rows("19:20").Select ActiveSheet.Shapes("Drop Down 1765").Select With Selection .ListFillRange = "mapping!$C$1:$C$53" .LinkedCell = "L20" .DropDownLines = 5 .Display3DShading = True End With End Sub The problem is that this wont work. Debug says that this: "Selection.AutoFill Destination:=Rows("19:20"), Type:=xlFillDefault", is a problem. Also I would like to button to create a new row at the bottom, and not as now copy row 19 and insert it to row 20. Hope this makes more sense. Thank you in advance for your help. /Phil |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert new row with drop-down box but problem with cell linkreferring to the original drop-down box
This may get you closer:
Option Explicit Sub testme() Dim myDD As DropDown Dim NextRow As Long Dim wks As Worksheet Dim wksList As Worksheet Dim ListRng As Range Set wks = Worksheets("Action plan") Set wksList = Worksheets("Mapping") With wksList Set ListRng = .Range("C1:c53") End With With wks 'uses column A to determine the last row NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 .Rows(NextRow - 1).Copy .Rows(NextRow).PasteSpecial Paste:=xlPasteFormulas With .Cells(NextRow, "K") Set myDD = .Parent.DropDowns.Add(Top:=.Top, _ Left:=.Left, Width:=.Width, Height:=.Height) End With With myDD .ListFillRange = ListRng.Address(external:=True) .LinkedCell = .Parent.Cells(NextRow, "L").Address(external:=True) .DropDownLines = 5 .Display3DShading = True End With End With End Sub On 07/07/2010 04:44, Phi| wrote: When I try recording a new macro this is what I get: Sub Makro4() ' ' Makro4 Makro ' ' ActiveSheet.DropDowns.Add(1990.5, 484.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 484.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 484.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 484.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(711.75, 488.25, 86.25, 21).Select ActiveSheet.DropDowns.Add(664.5, 488.25, 34.5, 21).Select ActiveSheet.DropDowns.Add(510, 488.25, 34.5, 21).Select ActiveSheet.DropDowns.Add(569.25, 488.25, 86.25, 21).Select ActiveSheet.DropDowns.Add(967.5, 487.5, 78, 19.5).Select ActiveSheet.DropDowns.Add(453.75, 488.25, 34.5, 21).Select ActiveSheet.DropDowns.Add(920.25, 488.25, 34.5, 21).Select ActiveSheet.DropDowns.Add(1207.5, 487.5, 45.75, 19.5).Select ActiveSheet.DropDowns.Add(1289.25, 487.5, 45.75, 19.5).Select ActiveSheet.DropDowns.Add(1365, 487.5, 45.75, 19.5).Select ActiveSheet.DropDowns.Add(1551, 487.5, 80.25, 19.5).Select ActiveSheet.DropDowns.Add(1707, 487.5, 63, 19.5).Select ActiveSheet.DropDowns.Add(2525.25, 487.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select Selection.AutoFill Destination:=Rows("19:20"), Type:=xlFillDefault Rows("19:20").Select End Sub and If I record a macro where I change the cell link I get: Sub Makro5() ' ' Makro5 Makro ' ' ActiveSheet.DropDowns.Add(1990.5, 484.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 484.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 484.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 484.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(711.75, 488.25, 86.25, 21).Select ActiveSheet.DropDowns.Add(664.5, 488.25, 34.5, 21).Select ActiveSheet.DropDowns.Add(510, 488.25, 34.5, 21).Select ActiveSheet.DropDowns.Add(569.25, 488.25, 86.25, 21).Select ActiveSheet.DropDowns.Add(967.5, 487.5, 78, 19.5).Select ActiveSheet.DropDowns.Add(453.75, 488.25, 34.5, 21).Select ActiveSheet.DropDowns.Add(920.25, 488.25, 34.5, 21).Select ActiveSheet.DropDowns.Add(1207.5, 487.5, 45.75, 19.5).Select ActiveSheet.DropDowns.Add(1289.25, 487.5, 45.75, 19.5).Select ActiveSheet.DropDowns.Add(1365, 487.5, 45.75, 19.5).Select ActiveSheet.DropDowns.Add(1551, 487.5, 80.25, 19.5).Select ActiveSheet.DropDowns.Add(1707, 487.5, 63, 19.5).Select ActiveSheet.DropDowns.Add(2525.25, 487.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select Selection.AutoFill Destination:=Rows("19:20"), Type:=xlFillDefault Rows("19:20").Select ActiveSheet.Shapes("Drop Down 1765").Select With Selection .ListFillRange = "mapping!$C$1:$C$53" .LinkedCell = "L20" .DropDownLines = 5 .Display3DShading = True End With End Sub The problem is that this wont work. Debug says that this: "Selection.AutoFill Destination:=Rows("19:20"), Type:=xlFillDefault", is a problem. Also I would like to button to create a new row at the bottom, and not as now copy row 19 and insert it to row 20. Hope this makes more sense. Thank you in advance for your help. /Phil -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create 2 dependent drop down lists from 1 original drop dow | Excel Discussion (Misc queries) | |||
insert a drop down list if a certain cell value is true | Excel Worksheet Functions | |||
Insert drop-down into a cell? | Excel Worksheet Functions | |||
I need to insert a drop down menu in a cell? | Excel Worksheet Functions | |||
How do I insert a drop down list in a cell using | Excel Worksheet Functions |