[kobi129]

: What this code does is to pull the decription from the excel spreadsheet fill the next cell (Column B) when click the drop down in Column A. Will appreciate your help. Thanks..



Sub Equipment_Group_change() [b]'error on Sub Equipment_Group_change()
' Sub procedure definition
'Private Sub controlsheet1()

'End Sub
Dim MyUniqueList As Variant 'error on Dim MyUniqueLine As Variant
Dim i As Variant
Dim cl As Range
Dim usr_Form As Variant
With usr_Form.cbo_pos

.Clear ' clear the drop down list content
MyUniqueList = PosItemList(tbl_2_Current_Location_Register.Range("E:E9"), True)
For i = 1 To UBound(MyUniqueList)
.AddItem MyUniqueList(i)
Next i
.ListIndex = 0 ' select the first item
End With
'End Sub
Private Function PosItemList(InputRange As Range, HorizontalList As Boolean) As Variant

'End Function
Dim cl As Range, cUnique As New Collection, i As Variant, uList() As Variant

Application.Volatile

On Error Resume Next

For Each cl In InputRange

If cl.Formula <> "" Then

cUnique.Add cl.Value, CStr(cl.Value)
End If
Next cl
PosItemList = ""
If cUnique.Count > 0 Then
ReDim uList(1 To cUnique.Count)
For i = 1 To cUnique.Count
uList(i) = cUnique(i)
Next i
PosItemList = uList
If Not HorizontalList Then
PosItem = Application.WorksheetFunction.Transpose(PosItemList)

End If
End If
On Error GoTo 0
End Function