Creating dropdown menu

[emmanuel]

Hi, I have an excel workbook, in which one worksheet has the following format.

Part name Machine name
xxx machine 1 machine2 machine3 ......
yyy machine3 machine6 machin10 ......
.
.
.
and so on..

Now, I have a code in which it copies some part names to another worksheet in a row. Now I want the machine names of each part to appear in a dropdown menu next to the corresponding part name. I have written a code to create the dropdown menu. But it does not take the values from another worksheet. The following is my code

 For l = 2 To a + 1
    Set objDataRangeStart = Worksheets("Output").Cells(1, m)
Set objDataRangeEnd = Worksheets("Output").Cells(100, m)
Set objCell = Worksheets("Output").Cells(l, 3)
With objCell.Validation
 .Delete
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & objDataRangeStart.Address & ":" & objDataRangeEnd.Address
 .IgnoreBlank = True
 .InCellDropdown = True
 .ErrorTitle = "Warning"
 .ErrorMessage = "Please select a value from the list available in the selected cell."
 .ShowError = True
End With
m = m + 1
Next l
End Sub

here in this code, what i tried was to copy the machine names to some column of the output worksheet and then put it in dropdown menu. But there are almost 7000 parts, for which this code cannot work. Can you please help me in writing a code for this?

Please... I tried different ways... nothing is working out

 

[Stoneboysteve]

Hi Emmanuel,

If you already have the data on the other sheet, you do not need code to create a dropdown. Select the list, in the name range box give the range a name. Select your target cells on the other sheet. Select data>validation>list, enter the name of the list with the equal sign: =rangename, enter the other information like title, etc.

Stoneboysteve