Hi,
I have wire remittance form and would like to have combobox display names for autocomplete found the code below been trying .Listrange "Vendors" which is the sheet that have all vendors name, could you please help me how to get sheet vendors to display in combobox the cell D6 is where the combobox is placed.
here is the code:
thank you
I have wire remittance form and would like to have combobox display names for autocomplete found the code below been trying .Listrange "Vendors" which is the sheet that have all vendors name, could you please help me how to get sheet vendors to display in combobox the cell D6 is where the combobox is placed.
here is the code:
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2020/01/16
Dim xCombox As OLEObject
Dim xStr As String
Dim xWs As Worksheet
Dim xArr
Set xWs = Application.ActiveSheet
On Error Resume Next
Set xCombox = xWs.OLEObjects("TempCombo")
With xCombox
[COLOR=rgb(209, 72, 65)][B].ListFillRange = ""[/B][/COLOR]
[B][COLOR=rgb(209, 72, 65)] .LinkedCell = "D6"[/COLOR][/B]
[COLOR=rgb(209, 72, 65)][B] .Visible = True[/B][/COLOR]
End With
If Target.Validation.Type = 3 Then
Target.Validation.InCellDropdown = False
Cancel = True
xStr = Target.Validation.Formula1
xStr = Right(xStr, Len(xStr) - 1)
If xStr = "" Then Exit Sub
With xCombox
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = xStr
If .ListFillRange = "" Then
xArr = Split(xStr, ",")
Me.TempCombo.List = xArr
End If
.LinkedCell = Target.Address
End With
xCombox.Activate
Me.TempCombo.DropDown
End If
End Sub
thank you