Hi everyone
I have an Excel-sheet where i have some columns. For some of the columns i have lists for each cell in the column. The vba code below makes sure that i for some of the columns can have several items from the list within each cell and some free text. The problem is for the other columns where i dont have lists but just want to write some text, when i try delete some of the text and go out of the cell the text i have deleted comes right back. Does anyone know how to modify the code below so i for the columns the have cells with with no lists, can write some text, delete the things i want, and make sure that the text i have deleted does not come back?
Hope it makes sense.
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 = 7 Or 3 Or 4 Or 6 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
I have an Excel-sheet where i have some columns. For some of the columns i have lists for each cell in the column. The vba code below makes sure that i for some of the columns can have several items from the list within each cell and some free text. The problem is for the other columns where i dont have lists but just want to write some text, when i try delete some of the text and go out of the cell the text i have deleted comes right back. Does anyone know how to modify the code below so i for the columns the have cells with with no lists, can write some text, delete the things i want, and make sure that the text i have deleted does not come back?
Hope it makes sense.
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 = 7 Or 3 Or 4 Or 6 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