为SharePoint链接Excel表创建可搜索文本框的VBA方案咨询

更简便的无代码实现方案（FILTER函数方案） #

优先推荐用FILTER函数实现，完全不需要编写VBA，对SharePoint链接表的兼容性远高于宏，不会出现运行时错误，数据同步后会自动刷新筛选结果：

• 操作步骤：
  1. 用Power Query将3个分类的SharePoint链接表合并为1个总汇总表，设置自动刷新即可和SharePoint List数据实时同步。
  2. 在汇总表设置两个输入控件：1个单元格用来输入搜索关键词，1个下拉框（用数据验证实现）用来选择搜索范围（文献标题/关键词）。
  3. 输出区域输入公式：

     =FILTER(总表数据范围, ISNUMBER(SEARCH(关键词单元格, 选择的搜索列)), "无匹配结果")
     

     如果需要支持同时搜索标题和关键词，可以改成多条件判断：

     =FILTER(总表数据范围, ISNUMBER(SEARCH(关键词单元格, 标题列)) + ISNUMBER(SEARCH(关键词单元格, 关键词列))>0, "无匹配结果")
     

现有VBA代码的报错原因和修复方案 #

如果你坚持要使用VBA方案，你的1004错误主要由以下几个问题导致：

1. 引用工作表用了ActiveSheet，如果运行宏时当前激活的不是目标工作表，会导致范围读取错误。建议替换为指定工作表名：Set sht = ThisWorkbook.Worksheets("你的工作表名称")
2. 单选按钮文本和表头不匹配时，Match函数会直接报错，导致myField参数无效。需要给Match加错误捕获，匹配失败时提示用户后直接退出过程。
3. 数据源范围写死为A5:F100，SharePoint链接表行数会动态变化，超出范围就会报错。建议启用你注释掉的表对象引用方式：Set DataRange = sht.ListObjects("EIATable").Range，自动适配表的动态范围。
4. 搜索内容为空时，通配符拼接后为=**，部分SharePoint链接表不支持该筛选规则，需要先判断搜索内容为空时直接重置筛选，不执行过滤。

修复后的代码参考：

Option Explicit

Sub EIASearch()
    Dim myButton As OptionButton
    Dim ButtonName As String
    Dim sht As Worksheet
    Dim myField As Long
    Dim DataRange As Range
    Dim mySearch As String
   
    '指定目标工作表，不要用ActiveSheet
    Set sht = ThisWorkbook.Worksheets("你的工作表名")

    '先重置筛选
    On Error Resume Next
    sht.ShowAllData
    On Error GoTo 0
   
    '引用链接表对象，自动适配范围
    On Error Resume Next
    Set DataRange = sht.ListObjects("EIATable").Range
    If Err.Number <> 0 Then
        MsgBox "未找到对应数据表，请检查表名是否正确"
        Exit Sub
    End If
    On Error GoTo 0

    '读取搜索内容
    mySearch = Trim(sht.Shapes("EIABox").TextFrame.Characters.Text)
    If mySearch = "" Then
        MsgBox "请输入搜索内容"
        Exit Sub
    End If

    '读取选中的搜索列
    ButtonName = ""
    For Each myButton In sht.OptionButtons
        If myButton.Value = 1 Then
            ButtonName = myButton.Text
            Exit For
        End If
    Next myButton
    If ButtonName = "" Then
        MsgBox "请选择搜索列"
        Exit Sub
    End If
  
    '匹配列号，加错误捕获
    On Error Resume Next
    myField = Application.WorksheetFunction.Match(ButtonName, DataRange.Rows(1), 0)
    If Err.Number <> 0 Or myField = 0 Then
        MsgBox "搜索列和表头不匹配，请检查单选按钮文本"
        Exit Sub
    End If
    On Error GoTo 0
  
    '执行筛选
    DataRange.AutoFilter _
        Field:=myField, _
        Criteria1:="=*" & mySearch & "*", _
        Operator:=xlAnd
  
    '清空搜索框
    sht.Shapes("EIABox").TextFrame.Characters.Text = ""
End Sub

内容的提问来源于stack exchange，提问作者Ollie Rooke