with excel vba code, how can return the cell it's previous state when deleting seleted option in the cell?

113 Views Asked by At

I have a simple concern about returning cell to its previous state (normally cell is including data validation list generated with DATA>DATA VALIDATION section) for a range of cells.

I have a macro which allows me to delete data validation list in the cell when selecting 'none' option from the dropdown list.

This is the code I am using.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cells As Range
    Dim DV As Range
    Dim isect2 As Range
    Dim valid As Validation
    Set DV = Range("K3:K28")
    Set isect2 = Intersect(Target, DV)
    Set valid = Selection.Validation
    If isect2 Is Nothing Then
        Exit Sub
    Else
        For Each Cells In isect2
            If Cells.Value = "None" Then
                Set valid = Selection.Validation
                Selection.Validation.Delete
            Else
                **Application.EnableEvents = False**
            End If
        Next Cells
    End If
End Sub

I would like to return the cell to its previous state (recovering same data validation list) when the selected option ('none') is deleted in the cell.

Edit

Sub Macro7(ByVal Target As Range)

Dim Cells As Range
Dim DV As Range
Dim isect2 As Range
Dim valid As Validation

    Set DV = Range("K3:K28")
    Set isect2 = Intersect(Target, DV)
    
    Set valid = Selection.Validation
    If isect2 Is Nothing Then
        Exit Sub
    Else
        For Each Cells In isect2    
            If Cells.Value = "None" Then
        
                Set valid = Selection.Validation
                Selection.Validation.Delete
        
            ElseIf IsEmpty(Cells.Value) = True Then
        
                With Selection.Validation
                    .Delete
                    .***Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=sheet4!$A$2:$A$18"***        
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = ""
                    .ShowInput = True
                    .ShowError = True
                End With
        
            End If    
        Next Cells
    End If
End Sub
1

There are 1 best solutions below

3
Tim Williams On BEST ANSWER

This worked for me:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim c As Range, isect2 As Range
    
    Set isect2 = Intersect(Target, Me.Range("K3:K28"))
    
    If Not isect2 Is Nothing Then
        For Each c In isect2.Cells
            If c.Value = "None" Then
                c.Validation.Delete
            ElseIf IsEmpty(c.Value) Then
                With c.Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                         Operator:=xlBetween, Formula1:="=sheet4!$A$2:$A$18"
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .ShowInput = True
                    .ShowError = True
                End With
            End If
        Next c
    End If
End Sub