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
This worked for me: