如何從串列中洗掉單元格中的值

我試圖弄清楚如何洗掉來自單元格串列的值。

我所擁有的是一個串列，您可以在其中選擇多個值。問題是我需要為每個選擇打開串列。

第二個問題是，如果我想洗掉一個值，我必須將它們全部洗掉，然后再次選擇。

如果有人對如何改進我所擁有的有任何想法，我將不勝感激。

供參考：這是我在之前的 excel 表中添加的 VBA 代碼：

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.Column = 5 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

uj5u.com熱心網友回復：

請測驗下一個更新的代碼。它檢查新選擇的字串是否已經存在于串列中，如果存在，則要求排除。按Yes，將被排除：

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String, Newvalue As String, ans As VbMsgBoxResult

If Target.cells.count > 1 Then Exit Sub 'if more than one cell changed (by copying, for example) code exists
If Not hasLValidation(Target) Then Exit Sub 'if no List validtion code exits
If Target.Value = "" Then Exit Sub

On Error GoTo Exitsub
If Target.Column = 5 Then
    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
                  ans = MsgBox("Do you like excluding """ & Newvalue & """ from the list?" & vbCrLf & _
                               "For excluding, please press ""Yes""!", vbYesNo, "Exclusion confirmation")
                      If ans <> vbYes Then
                            Target.Value = Oldvalue
                      Else
                            Dim arr, mtch
                            arr = Split(Oldvalue, ", ")   'place the list in an array
                            mtch = Application.match(Newvalue, arr, 0) 'match the array element
                            If Not IsError(mtch) Then    'if a match exists:
                                arr(mtch - 1) = "@#$%&"  'replace that element with a strange string different from all existing
                                arr = filter(arr, "@#$%&", False) 'eliminate that specific element

                                Target.Value = Join(arr, ", ")       'place back the list by joining the array
                            End If
                      End If
             End If
    End If
End If

Exitsub:
Application.EnableEvents = True
End Sub

Function hasLValidation(T As Range) As Boolean
    Dim vType As Long
    On Error Resume Next
       vType = T.Validation.Type
    On Error GoTo 0
    If vType = 3 Then hasLValidation = True 'only for LIST validation type!
End Function

請在測驗后發送一些反饋。

標籤：擅长vba

上一篇：在vba中打開Excel作業簿

下一篇：根據Order_Num和Cust_Num中的重復值選擇范圍