Apologies for my low level of Excel understanding, maybe what I am looking to do is not possible.
I have a list of 120 entries that I want to use as data validation. But instead of people having to scroll down the 120 options it would be great if they can start typing and see the options that could match what they are willing to write. Kind of like in autocomplete, I have found this tip.
I would like to have more options when the typing starts.
For example this would be a part of the list:
Awareness
Education
Budget
Budget Planning
Enterprise Budget
When typing "B" I would like the three last options to appear and to click one of them. I don't know if there is a way of include vlookup in here...
Ideally the perfect thing would be to have several "tags" in one cell, but I fear that is absolutely impossible with excel.
Thank you very much for any input and sorry if I haven't explained myself properly.
This question is related to
excel
validation
tags
I adapted the answer by ChrisB. Like in his example a temporary combobox is made visible when a cell is clicked. Additionally:
Option Explicit_x000D_
_x000D_
Private Const DATA_RANGE = "A1:A16"_x000D_
Private Const DROPDOWN_RANGE = "F2:F10"_x000D_
Private Const HELP_COLUMN = "$G"_x000D_
_x000D_
_x000D_
Private Sub Worksheet_SelectionChange(ByVal target As Range)_x000D_
Dim xWs As Worksheet_x000D_
Set xWs = Application.ActiveSheet_x000D_
_x000D_
On Error Resume Next_x000D_
_x000D_
With Me.TempCombo_x000D_
.LinkedCell = vbNullString_x000D_
.Visible = False_x000D_
End With_x000D_
_x000D_
If target.Cells.count > 1 Then_x000D_
Exit Sub_x000D_
End If_x000D_
_x000D_
Dim isect As Range_x000D_
Set isect = Application.Intersect(target, Range(DROPDOWN_RANGE))_x000D_
If isect Is Nothing Then_x000D_
Exit Sub_x000D_
End If_x000D_
_x000D_
With Me.TempCombo_x000D_
.Visible = True_x000D_
.Left = target.Left - 1_x000D_
.Top = target.Top - 1_x000D_
.Width = target.Width + 5_x000D_
.Height = target.Height + 5_x000D_
.LinkedCell = target.Address_x000D_
_x000D_
End With_x000D_
_x000D_
Me.TempCombo.Activate_x000D_
Me.TempCombo.DropDown_x000D_
End Sub_x000D_
_x000D_
Private Sub TempCombo_Change()_x000D_
If Me.TempCombo.Visible = False Then_x000D_
Exit Sub_x000D_
End If_x000D_
_x000D_
Dim currentValue As String_x000D_
currentValue = Range(Me.TempCombo.LinkedCell).Value_x000D_
_x000D_
If Trim(currentValue & vbNullString) = vbNullString Then_x000D_
Me.TempCombo.ListFillRange = "=" & DATA_RANGE_x000D_
Else_x000D_
If Me.TempCombo.ListIndex = -1 Then_x000D_
Dim listCount As Integer_x000D_
listCount = write_matching_items(currentValue)_x000D_
Me.TempCombo.ListFillRange = "=" & HELP_COLUMN & "1:" & HELP_COLUMN & listCount_x000D_
Me.TempCombo.DropDown_x000D_
End If_x000D_
_x000D_
End If_x000D_
End Sub_x000D_
_x000D_
_x000D_
Private Function write_matching_items(currentValue As String) As Integer_x000D_
Dim xWs As Worksheet_x000D_
Set xWs = Application.ActiveSheet_x000D_
_x000D_
Dim cell As Range_x000D_
Dim c As Range_x000D_
Dim firstAddress As Variant_x000D_
Dim count As Integer_x000D_
count = 0_x000D_
xWs.Range(HELP_COLUMN & ":" & HELP_COLUMN).Delete_x000D_
With xWs.Range(DATA_RANGE)_x000D_
Set c = .Find(currentValue, LookIn:=xlValues)_x000D_
If Not c Is Nothing Then_x000D_
firstAddress = c.Address_x000D_
Do_x000D_
Set cell = xWs.Range(HELP_COLUMN & "$" & (count + 1))_x000D_
cell.Value = c.Value_x000D_
count = count + 1_x000D_
_x000D_
Set c = .FindNext(c)_x000D_
If c Is Nothing Then_x000D_
GoTo DoneFinding_x000D_
End If_x000D_
Loop While c.Address <> firstAddress_x000D_
End If_x000D_
DoneFinding:_x000D_
End With_x000D_
_x000D_
write_matching_items = count_x000D_
_x000D_
End Function_x000D_
_x000D_
Private Sub TempCombo_KeyDown( __x000D_
ByVal KeyCode As MSForms.ReturnInteger, __x000D_
ByVal Shift As Integer)_x000D_
_x000D_
Select Case KeyCode_x000D_
Case 9 ' Tab key_x000D_
Application.ActiveCell.Offset(0, 1).Activate_x000D_
Case 13 ' Pause key_x000D_
Application.ActiveCell.Offset(1, 0).Activate_x000D_
End Select_x000D_
End Sub
_x000D_
Notes:
2 - fmMatchEntryNone
. Don't forget to set ComboBox name to TempCombo
ComboBox.addItem
, but it turned out to be hard to repaint list box as user typesIf you don't want to go down the VBA path, there is this trick from a previous question.
Excel 2010: how to use autocomplete in validation list
It does add some annoying bulk to the top of your sheets, and potential maintenance (should you need more options, adding names of people from a staff list, new projects etc.) but works all the same.
There's a messy workaround at http://www.ozgrid.com/Excel/autocomplete-validation.htm that basically works like this:
Tools - Options > Edit
;Why not use a Pivot?
This is a solution how to make autocomplete drop down list with VBA :
Firstly you need to insert a combo box into the worksheet and change its properties, and then running the VBA code to enable the autocomplete.
Get into the worksheet which contains the drop down list you want it to be autocompleted.
Before inserting the Combo box, you need to enable the Developer tab in the ribbon.
a). In Excel 2010 and 2013, click File > Options. And in the Options dialog box, click Customize Ribbon in the right pane, check the Developer box, then click the OK button.
b). In Outlook 2007, click Office button > Excel Options. In the Excel Options dialog box, click Popular in the right bar, then check the Show Developer tabin the Ribbon box, and finally click the OK button.
Then click Developer > Insert > Combo Box under ActiveX Controls.
Draw the combo box in current opened worksheet and right click it. Select Properties in the right-clicking menu.
Turn off the Design Mode with clicking Developer > Design Mode.
Right click on the current opened worksheet tab and click View Code.
Make sure that the current worksheet code editor is opened, and then copy and paste the below VBA code into it.
Code borrowed from extendoffice.com
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2018/9/21
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
.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
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
Private Sub TempCombo_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
Click File > Close and Return to Microsoft Excel to close the Microsoft Visual Basic for Application window.
Now, just click the cell with drop down list, you can see the drop-down list is displayed as a combo box, then type the first letter into the box, the corresponding word will be completed automatically.
Note: This VBA code is not applied to merged cells.
Source : How To Autocomplete When Typing In Excel Drop Down List?
None of the above mentioned solution worked. The one that seemed to work only provide the functionality for just one cell
Recently I had to enter a lot of names and without suggestions, it was a huge pain. I was fortunate enough to have this excel autocomplete add-in to enable the autocompletion. The down side is that you need to enable macro (but you can always turn it off later)
ExtendOffice.com offers a VBA solution that worked for me in Excel 2016. Here's my description of the steps. I included additional details to make it easier. I also modified the VBA code slightly. If this doesn't work for you, retry the steps or check out the instructions on the ExtendOffice page.
Add data validation to a cell (or range of cells). Allow = List. Source = [the range with the values you want for the auto-complete / drop-down]. Click OK. You should now have a drop-down but with a weak auto-complete feature.
With a cell containing your newly added data validation, insert an ActiveX combo box (NOT a form control combo box). This is done from the Developer ribbon. If you don't have the Developer ribbon you will need to add it from the Excel options menu.
From the Developer tab in the Controls section, click "Design Mode". Select the combo box you just inserted. Then in the same ribbon section click "Properties". In the Properties window, change the name of the combo box to "TempComboBox".
Press ALT + F11 to go to the Visual Basic Editor. On the left-hand side, double click the worksheet with your data validation to open the code for that sheet. Copy and paste the following code onto the sheet. NOTE: I modified the code slightly so that it works even with Option Explicit
enabled at the top of the sheet.
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal target As Range)
'Update by Extendoffice: 2018/9/21
' Update by Chris Brackett 2018-11-30
Dim xWs As Worksheet
Set xWs = Application.ActiveSheet
On Error Resume Next
Dim xCombox As OLEObject
Set xCombox = xWs.OLEObjects("TempCombo")
' Added this to auto select all text when activating the combox box.
xCombox.SetFocus
With xCombox
.ListFillRange = vbNullString
.LinkedCell = vbNullString
.Visible = False
End With
Dim xStr As String
Dim xArr
If target.Validation.Type = xlValidateList Then
' The target cell contains Data Validation.
target.Validation.InCellDropdown = False
' Cancel the "SelectionChange" event.
Dim Cancel As Boolean
Cancel = True
xStr = target.Validation.Formula1
xStr = Right(xStr, Len(xStr) - 1)
If xStr = vbNullString 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 = vbNullString Then
xArr = Split(xStr, ",")
Me.TempCombo.List = xArr
End If
.LinkedCell = target.Address
End With
xCombox.Activate
Me.TempCombo.DropDown
End If
End Sub
Private Sub TempCombo_KeyDown( _
ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Select Case KeyCode
Case 9 ' Tab key
Application.ActiveCell.Offset(0, 1).Activate
Case 13 ' Pause key
Application.ActiveCell.Offset(1, 0).Activate
End Select
End Sub
Make sure the the "Microsoft Forms 2.0 Object Library" is referenced. In the Visual Basic Editor, go to Tools > References, check the box next to that library (if not already checked) and click OK. To verify that it worked, go to Debug > Compile VBA Project.
Finally, save your project and click in a cell with the data validation you added. You should see a combo box with a drop-down list of suggestions that updates with each letter you type.
Source: Stackoverflow.com