Help on VBA code - multiple data validation - unpick

Dave87

Board Regular
Joined
Apr 22, 2020
Messages
107
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello,

I've got the following code (source Select Multiple Items from a Drop Down List in Excel and modified to affect my columns) which allows me to pick multiple drop downs in a cell but I'd like the option to unpick an item, at the minute if I pick 1 wrong drop down item I need to clear the cell and repick the correct ones only. Any suggestions?

As an example, if I pick "one", "two", and "four" but should have picked "three" instead of "four". I need to clear the cell and repick "one", "two", &"three"

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 43 Or Target.Column = 44 Or Target.Column = 45 Or Target.Column = 46 Or Target.Column = 47 Or Target.Column = 48 Or Target.Column = 49 Or Target.Column = 50 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

Thanks in advance
Dave87
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
This is an adaptation of the second code in the above link :

Run the UndoLast routine to undo the last entry.
VBA Code:
Option Explicit

Dim vUndoList() As Variant
Dim oTargetCell As Range

Private Sub Worksheet_Change(ByVal Target As Range)

    'Code by Sumit Bansal from https://trumpexcel.com
    ' To allow multiple selections in a Drop Down List in Excel (without repetition)

    Dim Oldvalue As String
    Dim Newvalue As String
    
    On Error Resume Next
        Dim n As Long
        n = UBound(vUndoList)
        If n = 0 Then ReDim Preserve vUndoList(0)
    On Error GoTo 0
    
    Application.EnableEvents = True
    On Error GoTo Exitsub
    If Target.Address = "$C$12" 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
        Set oTargetCell = Target
        ReDim Preserve vUndoList(UBound(vUndoList) + 1)
        vUndoList(UBound(vUndoList)) = Oldvalue
          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


Private Sub UndoLast()

    On Error GoTo errHandler
    
    If UBound(vUndoList) >= 1 Then
        Application.EnableEvents = False
        oTargetCell.Value = vUndoList(UBound(vUndoList))
        ReDim Preserve vUndoList(UBound(vUndoList) - 1)
    End If
errHandler:
    Application.EnableEvents = True

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top