Creating Dropdown menu

[rinijg]

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 ou

 

[rinijg]

Please help me out..

 

[Giordano Bruno]

Data validation doesn't work from a range address on another worksheet. You will need to refer to the cells by using a range name. Perhaps one that changes as the number of cell references increases. As an example, if your cell range is called "myRange", the data validation source should be "=myRange"

Does that help?

 

[rinijg]

How do i name the cell range? I am sorry, I am kinda new to this

 

[Giordano Bruno]

Suppose the machine names are in cells C4:C7 of Sheet1 and you add a name, "myRange" which is defined as =OFFSET(Sheet1!$C$4,0,0,COUNTA(Sheet1!$C$4:$C$100),1). This range will adjust its height to include any number of filled cells between C4 and C100.

Your code then need only be

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=myRange"