How to remove values from drop-down if value deleted from validation list

0 votes

One sheet in my workbook is a datasheet, and the other sheet has the values for data validation. When eliminating a value from a cell (in a datasheet), I'm having trouble (containing data validation). The problem is that even though I tried to remove the value from the validation list, it didn't appear to have been removed from the cell. (view screen grab) For instance, "the value didn't delete from a cell in the data sheet (cell indicated in the screenshot) if I wish to remove volunteer name from the validation list."

I created a VBA program to add several values to the same cell when they are separated by commas. I would welcome assistance in resolving this problem. Below is my VBA code:

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngDV As Range
    Dim oldVal As String
    Dim newVal As String
    Dim lUsed As Long
    If Target.Count > 1 Then GoTo exitHandler

    On Error Resume Next
    Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo exitHandler

    If rngDV Is Nothing Then GoTo exitHandler

    If Intersect(Target, rngDV) Is Nothing Then
    'do nothing
    Else
    Application.EnableEvents = False
    newVal = Target.Value
    Application.Undo
    oldVal = Target.Value
    Target.Value = newVal
    If Target.Column = 3 Or Target.Column = 4 Or Target.Column = 5 Or Target.Column = 6 Or 
    Target.Column = 7 Or Target.Column = 8 _
                       Or Target.Column = 9 Or Target.Column = 11 Then
                    
    If oldVal = "" Then
      'do nothing
      Else
      If newVal = "" Then
      'do nothing
      Else
        lUsed = InStr(1, oldVal, newVal)
        If lUsed > 0 Then
            If Right(oldVal, Len(newVal)) = newVal Then
                Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)
            Else
                Target.Value = Replace(oldVal, newVal & ", ", "")
            End If
        Else
            Target.Value = oldVal _
              & ", " & newVal
        End If
        
        End If
        End If
        End If
        End If

        exitHandler:
        Application.EnableEvents = True
        End Sub

You can find the worksheet from the link below: (show1 Sheet is the data sheet and Validation Fields contains the drop-down values)

Dec 11, 2022 in Others by Kithuzzz
• 28,700 points
52 views

1 answer to this question.

0 votes

Try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const SEP As String = ","
    Dim c As Range, NewValue As String, OldValue As String, arr, v, lst, removed As Boolean

    On Error GoTo Exitsub

    If Target.CountLarge > 1 Then Exit Sub '<< only handling single-cell changes

    Select Case Target.Column
        Case 3, 4, 5, 6, 7, 8, 9, 11
            Set c = Target
        Case Else: Exit Sub
    End Select
    
    If Len(c.Value) > 0 And Not c.Validation Is Nothing Then

        Application.EnableEvents = False
        NewValue = c.Value
        Application.Undo
        OldValue = c.Value

        If OldValue = "" Then
            c.Value = NewValue
        Else
            arr = Split(OldValue, SEP)
            'loop over previous list, removing newvalue if found
            For Each v In arr
                If Trim(CStr(v)) = NewValue Then
                    removed = True
                Else
                    lst = lst & IIf(lst = "", "", SEP) & v
                End If
            Next v
            'add the new value if we didn't just remove it
            If Not removed Then lst = lst & IIf(lst = "", "", SEP) & NewValue
            c.Value = lst
        End If
    End If    'has validation and non-empty
    
Exitsub:
    If Err.Number <> 0 Then MsgBox Err.Description
    Application.EnableEvents = True
End Sub
answered Dec 11, 2022 by narikkadan
• 53,160 points

Related Questions In Others

0 votes
1 answer

How to create a drop-down list in Excel?

Making a list of the items you ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 53,160 points
133 views
0 votes
1 answer

How to create a dependent drop down list using [Apache POI]

There is nothing apache poi cannot do, ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 53,160 points
798 views
0 votes
1 answer

Paste a value from a drop-down list

Check if there is an existing sheet ...READ MORE

answered Jan 14 in Others by narikkadan
• 53,160 points
44 views
0 votes
1 answer

How to remove debug banner from Flutter App?

Hi@akhtar, On your MaterialApp set debugShowCheckedModeBanner to false. The debug banner will also automatically be ...READ MORE

answered Jul 28, 2020 in Others by MD
• 95,420 points
1,371 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,630 points
175 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 53,160 points
284 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 53,160 points
195 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 53,160 points
274 views
0 votes
1 answer

Excel delete row if column contains value from to-remove-list

Given sheet 2: ColumnA ------- apple orange You can flag the rows ...READ MORE

answered Nov 6, 2022 in Others by narikkadan
• 53,160 points
255 views
0 votes
1 answer

How to create a drop-down in excel with custom values

You can accomplish that using code rather ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
• 53,160 points
148 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP