使用宏在Excel中进行MultiSelect，如何取消选择选择 [英] MultiSelect in Excel using Macros, how to un-select the Selection

问题描述

我需要使用MultiSelect下拉列表创建启用了宏的excelsheet.

I need to create Macro enabled excelsheet with MultiSelect dropdown.

1. 用户选择下拉菜单之一，然后使用逗号分隔将值附加到单元格中.
2. 如果用户再次选择已经选择的值，则应将其从列表中删除.

通过遵循以下代码，我可以实现第一部分，但不能达到第二部分.

I was able to achieve first part of it, by following code, but couldn't achieve second part.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Oldvalue As String
    Dim Newvalue As String
    Application.EnableEvents = True
    On Error GoTo Exitsub
    If Target.Address = "$D$2" 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 & ", " & Newvalue
          Else:
            Target.Value = Oldvalue
          End If
        End If
      End If
    End If
    Application.EnableEvents = True
    Exitsub:
    Application.EnableEvents = True
    End Sub

如何实现第二部分?请提出建议.

How to achieve part #2? Please suggest.

推荐答案

我建议使用类似@displayname的方法，但可以使用for循环并替换代码的其他部分.

I would suggest similar like @displayname, but would do with for loop and replace else part of your code.

  Dim substrings() As String
    substrings = Split(Oldvalue, ", ")
    Target.Value = ""
    Dim i As Integer
    For i = LBound(substrings) To UBound(substrings)
        If Not (substrings(i) = Newvalue) Then
          Target.Value = Target.Value & ", " & substrings(i)
        End If
    Next i

这篇关于使用宏在Excel中进行MultiSelect，如何取消选择选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！