PDA

View Full Version : inserting multiple drop down menus



mpfyffe
06-25-2013, 10:52 AM
I need to add a drop down menu to each row of data (thousands of lines of data)and when I try to copy the inserted drop down menu it copies every property of the cell including the output cell, how can I fill down the cells without having to go back to each menu and changing the output cell?

Here is the recorded macro code where I keep trying to do this:

Option Explicit
Sub adddropdownmenu()
'
' adddropdownmenu Macro
'
' Keyboard Shortcut: Ctrl+d
'

ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.DropDowns.Add(762, 201, 77.25, 15.75).Select
With Selection
.ListFillRange = "$J$7:$J$9"
.LinkedCell = ""
.DropDownLines = 8
.Display3DShading = False
End With
End Sub

mpfyffe
06-25-2013, 02:36 PM
I emailed my file to a member of this forum and here was there reply:

On the Master Bid file, do not use Form or Active X controls like you do in Column J. Those are hard to add with code and if there are too many the file will crash. Instead just use Data Validation which will put the drop-down in the cell. You can format the entire column to have the drop-down so as new rows are started the drop-down validation is available.