工作表宏冲突：列表框多选宏失效，仅支持单项选择求助

我在Excel工作表中编写了两类宏：

• 实现下拉列表的多项选择功能（不重复）
• 通过临时导航按钮切换工作表标签并隐藏不必要的标签

原本第一个宏可正常实现多选添加，但添加导航相关宏后，该宏失效，仅能添加单项选择。

原代码如下：

' To allow multiple selections in a Drop Down List in Excel (without repetition)
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("table19")) Is Nothing Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & vbNewLine & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub


'<---- Start of Nav Link Cod---->
Private Sub Label1_Click()
End Sub
Private Sub CommandButton1_Click()
Sheets("LIST_locations_LIST").Visible = True
 Sheets("LIST_locations_LIST").Select
End Sub
Private Sub CommandButton2_Click()
Sheets("LIST_Schedule_contact_LIST").Visible = True
 Sheets("LIST_Schedule_contact_LIST").Select
End Sub
Private Sub CommandButton3_Click()
Sheets("LIST_Admin_LIST").Visible = True
 Sheets("LIST_Admin_LIST").Select
End Sub
Private Sub CommandButton4_Click()
Sheets("LIST_System_Owner_LIST").Visible = True
 Sheets("LIST_System_Owner_LIST").Select
End Sub
Private Sub CommandButton5_Click()
Sheets("LIST_Vendor_contacts_LIST").Visible = True
 Sheets("LIST_Vendor_Contacts_LIST").Select
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   On Error Resume Next
    With ActiveSheet.Shapes("Label1")
        .Top = Target.Offset(1).Top
        .Left = Target.Offset(, 1).Left
    End With
     With ActiveSheet.Shapes("CommandButton1")
        .Top = Target.Offset(3).Top
        .Left = Target.Offset(, 1).Left
    End With
    With ActiveSheet.Shapes("CommandButton2")
        .Top = Target.Offset(5).Top
        .Left = Target.Offset(, 1).Left
    End With
      With ActiveSheet.Shapes("CommandButton3")
        .Top = Target.Offset(7).Top
        .Left = Target.Offset(, 1).Left
    End With
      With ActiveSheet.Shapes("CommandButton4")
        .Top = Target.Offset(9).Top
        .Left = Target.Offset(, 1).Left
    End With
      With ActiveSheet.Shapes("CommandButton5")
        .Top = Target.Offset(11).Top
        .Left = Target.Offset(, 1).Left
    End With
End Sub
'<---- End of Nav Link Cod---->

1. Worksheet_Change事件中Application.EnableEvents的逻辑混乱，初始设置为True，后续禁用后恢复逻辑不够严谨，可能导致事件被意外关闭
2. Worksheet_SelectionChange使用全局On Error Resume Next，会掩盖其他事件的错误，干扰Worksheet_Change的正常触发
3. 导航按钮切换工作表时，未确保事件始终处于启用状态
4. 导航按钮代码中存在工作表名称大小写不一致的问题（CommandButton5中LIST_Vendor_contacts_LIST与LIST_Vendor_Contacts_LIST）

多选功能宏 #

' 实现下拉列表多选（无重复）
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Oldvalue As String
    Dim Newvalue As String
    Dim hasValidation As Boolean
    
    ' 先禁用事件，避免循环触发
    Application.EnableEvents = False
    On Error GoTo Exitsub
    
    ' 仅处理指定范围且非空的单元格
    If Not Intersect(Target, Me.Range("table19")) Is Nothing And Target.Value <> "" Then
        ' 检查单元格是否包含数据验证
        On Error Resume Next
        hasValidation = Not Target.Validation Is Nothing
        On Error GoTo Exitsub
        
        If hasValidation Then
            Newvalue = Target.Value
            Application.Undo
            Oldvalue = Target.Value
            
            ' 新值不存在则追加，存在则保持原值
            If Oldvalue = "" Then
                Target.Value = Newvalue
            ElseIf InStr(1, Oldvalue, Newvalue, vbTextCompare) = 0 Then
                Target.Value = Oldvalue & vbNewLine & Newvalue
            End If
        End If
    End If
    
Exitsub:
    ' 无论是否出错，都恢复事件启用状态
    Application.EnableEvents = True
End Sub

导航功能宏 #

'<---- 导航按钮代码开始---->
Private Sub Label1_Click()
End Sub

Private Sub CommandButton1_Click()
    With Sheets("LIST_locations_LIST")
        .Visible = True
        .Select
    End With
    Application.EnableEvents = True
End Sub

Private Sub CommandButton2_Click()
    With Sheets("LIST_Schedule_contact_LIST")
        .Visible = True
        .Select
    End With
    Application.EnableEvents = True
End Sub

Private Sub CommandButton3_Click()
    With Sheets("LIST_Admin_LIST")
        .Visible = True
        .Select
    End With
    Application.EnableEvents = True
End Sub

Private Sub CommandButton4_Click()
    With Sheets("LIST_System_Owner_LIST")
        .Visible = True
        .Select
    End With
    Application.EnableEvents = True
End Sub

Private Sub CommandButton5_Click()
    ' 统一工作表名称大小写，避免引用错误
    With Sheets("LIST_Vendor_Contacts_LIST")
        .Visible = True
        .Select
    End With
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' 逐个处理控件，避免全局错误掩盖问题
    On Error Resume Next
    Me.Shapes("Label1").Top = Target.Offset(1).Top
    Me.Shapes("Label1").Left = Target.Offset(, 1).Left
    On Error GoTo 0
    
    On Error Resume Next
    Me.Shapes("CommandButton1").Top = Target.Offset(3).Top
    Me.Shapes("CommandButton1").Left = Target.Offset(, 1).Left
    On Error GoTo 0
    
    On Error Resume Next
    Me.Shapes("CommandButton2").Top = Target.Offset(5).Top
    Me.Shapes("CommandButton2").Left = Target.Offset(, 1).Left
    On Error GoTo 0
    
    On Error Resume Next
    Me.Shapes("CommandButton3").Top = Target.Offset(7).Top
    Me.Shapes("CommandButton3").Left = Target.Offset(, 1).Left
    On Error GoTo 0
    
    On Error Resume Next
    Me.Shapes("CommandButton4").Top = Target.Offset(9).Top
    Me.Shapes("CommandButton4").Left = Target.Offset(, 1).Left
    On Error GoTo 0
    
    On Error Resume Next
    Me.Shapes("CommandButton5").Top = Target.Offset(11).Top
    Me.Shapes("CommandButton5").Left = Target.Offset(, 1).Left
    On Error GoTo 0
End Sub
'<---- 导航按钮代码结束---->

• 重构Worksheet_Change的事件控制逻辑，确保事件始终能正确恢复
• 将Worksheet_SelectionChange的全局错误处理改为逐个控件单独处理，避免干扰其他事件
• 修复导航按钮中工作表名称的大小写不一致问题
• 在每个导航按钮点击事件末尾明确恢复事件启用状态，防止切换工作表后事件被禁用

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