带有建议/自动完成功能的 Excel 数据验证 [英] Excel data validation with suggestions/autocomplete
问题描述
为我对 Excel 的理解水平低表示歉意，也许我想做的事情是不可能的.
我有一个包含 120 个条目的列表，我想将其用作数据验证.但是，人们不必向下滚动 120 个选项，如果他们可以开始输入并查看与他们愿意写的内容相匹配的选项，那就太好了.有点像在自动完成中，我找到了
- 使用包含新添加的数据验证的单元格，插入 ActiveX 组合框(不是表单控件组合框).这是从开发人员功能区完成的.如果您没有开发人员功能区，则需要从 Excel 选项菜单中添加它.
- 在控件"部分的开发工具"选项卡中，单击设计模式".选择您刚刚插入的组合框.然后在同一功能区部分单击属性".在属性"窗口中，将组合框的名称更改为TempCombo".
按 ALT + F11 转到 Visual Basic 编辑器.在左侧，双击带有数据验证的工作表以打开该工作表的代码.将以下代码复制并粘贴到工作表上.注意:我稍微修改了代码，以便即使在工作表顶部启用
Option Explicit也能正常工作.选项显式Private Sub Worksheet_SelectionChange(ByVal target As Range)'扩展办公室更新:2018/9/21' 克里斯·布拉克特 (Chris Brackett) 更新 2018-11-30Dim xWs 作为工作表设置 xWs = Application.ActiveSheet出错时继续下一步将 xCombox 调暗为 OLEObject设置 xCombox = xWs.OLEObjects("TempCombo")' 添加此项以在激活组合框时自动选择所有文本.xCombox.SetFocus使用 xCombox.ListFillRange = vbNullString.LinkedCell = vbNullString.可见=假结束于将 xStr 调暗为字符串昏暗的 xArr如果 target.Validation.Type = xlValidateList 那么' 目标单元格包含数据验证.target.Validation.InCellDropdown = False' 取消SelectionChange"；事件.Dim Cancel 作为布尔值取消 = 真xStr = target.Validation.Formula1xStr = Right(xStr, Len(xStr) - 1)如果 xStr = vbNullString 然后退出子使用 xCombox.可见 = 真.Left = target.Left.Top = 目标.Top.Width = 目标.Width + 5.Height = 目标.Height + 5.ListFillRange = xStr如果 .ListFillRange = vbNullString 那么xArr = Split(xStr, ,")Me.TempCombo.List = xArr万一.LinkedCell = target.Address结束于xCombox.ActivateMe.TempCombo.DropDown万一结束子私有子 TempCombo_KeyDown( _ByVal KeyCode As MSForms.ReturnInteger, _ByVal 移位为整数)选择案例密钥代码Case 9 ' Tab 键Application.ActiveCell.Offset(0, 1).ActivateCase 13 ' 暂停键Application.ActiveCell.Offset(1, 0).Activate结束选择结束子确保Microsoft Forms 2.0 Object Library"被引用.在 Visual Basic 编辑器中，转到工具 >参考，选中该库旁边的框(如果尚未选中)，然后单击确定.要验证它是否有效，请转到调试 >编译 VBA 项目.
最后，保存您的项目并单击包含您添加的数据验证的单元格.您应该会看到一个带有建议下拉列表的组合框，该列表会随着您输入的每个字母而更新.
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.
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 "TempCombo".
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 Explicitenabled 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 SubMake 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.
这篇关于带有建议/自动完成功能的 Excel 数据验证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
