Hi everybody. I'm having issues with setting up multiple drop-down boxes to auto-complete with a set list of data values. I've got two tabs that have multiple drop-down boxes utilizing the same data values from a third tab. Because some of the drop-downs have multiple selections (Country for instance), I'd like to have these drop-down boxes auto-complete when the user clicks on the drop-down and starts typing.
I found the below code for combo boxes and applied it to both tabs of my workbook (creating a separate combo box on each tab and giving them different names) and it works as intended... Weird thing though.. After populating the combo-boxes and running a macro to copy the data and paste, it appears as though a new drop-down is being added randomly on the tab.. when I click on it, it just disappears... or if I click on the other tab and then back it's gone. If I only have a combo box on one tab and just standard drop-downs on the other tab, there are no issues.. so it appears as though the weird issue is caused by having combo boxes on both tabs..
Anyone run across something similar? Is there a simple fix for this? Is there an easier way to set up auto-complete to a series of drop-down boxes?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2017/8/15
Dim xCombox As OLEObject
Dim xStr As String
Dim xWs As Worksheet
Set xWs = Application.ActiveSheet
On Error Resume Next
Set xCombox = xWs.OLEObjects("ComboBox1")
With xCombox
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
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
.LinkedCell = Target.Address
End With
xCombox.Activate
Me.ComboBox1.DropDown
End If
End Sub
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case 9
Application.ActiveCell.Offset(0, 1).Activate
Case 13
Application.ActiveCell.Offset(1, 0).Activate
End Select
End Sub
I found the below code for combo boxes and applied it to both tabs of my workbook (creating a separate combo box on each tab and giving them different names) and it works as intended... Weird thing though.. After populating the combo-boxes and running a macro to copy the data and paste, it appears as though a new drop-down is being added randomly on the tab.. when I click on it, it just disappears... or if I click on the other tab and then back it's gone. If I only have a combo box on one tab and just standard drop-downs on the other tab, there are no issues.. so it appears as though the weird issue is caused by having combo boxes on both tabs..
Anyone run across something similar? Is there a simple fix for this? Is there an easier way to set up auto-complete to a series of drop-down boxes?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2017/8/15
Dim xCombox As OLEObject
Dim xStr As String
Dim xWs As Worksheet
Set xWs = Application.ActiveSheet
On Error Resume Next
Set xCombox = xWs.OLEObjects("ComboBox1")
With xCombox
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
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
.LinkedCell = Target.Address
End With
xCombox.Activate
Me.ComboBox1.DropDown
End If
End Sub
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case 9
Application.ActiveCell.Offset(0, 1).Activate
Case 13
Application.ActiveCell.Offset(1, 0).Activate
End Select
End Sub
