llyamah
02-28-2012, 10:17 AM
Hello All
Could someone help me with this code. This is what I want it to do:
When the user is presented with a drop down box in column C, the user is presented with the options 1,2,3, 4, 5 and so on. The code is supposed to allow the user to select as many numbers as they want, and they appear below each other with a line break. The code does this fine.
However, the code is also supposed to let the user delete selections made: if they select 1,2,3 and 4, they should be able to reselect '3' and that deletes '3' from the cell. However, it only works insofar as it allows the user to delete the last entry (in this case '4') and not the other entries.
I hope I've explained this okay. Grateful for any help that anyone can offer. I should point out that this is some code that I've found (and modified) on the internet.
Thanks.
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 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) - 1)
Else
Target.Value = Replace(oldVal, newVal & ", ", "")
End If
Else
Target.Value = oldVal _
& vbLf & newVal
End If
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
Could someone help me with this code. This is what I want it to do:
When the user is presented with a drop down box in column C, the user is presented with the options 1,2,3, 4, 5 and so on. The code is supposed to allow the user to select as many numbers as they want, and they appear below each other with a line break. The code does this fine.
However, the code is also supposed to let the user delete selections made: if they select 1,2,3 and 4, they should be able to reselect '3' and that deletes '3' from the cell. However, it only works insofar as it allows the user to delete the last entry (in this case '4') and not the other entries.
I hope I've explained this okay. Grateful for any help that anyone can offer. I should point out that this is some code that I've found (and modified) on the internet.
Thanks.
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 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) - 1)
Else
Target.Value = Replace(oldVal, newVal & ", ", "")
End If
Else
Target.Value = oldVal _
& vbLf & newVal
End If
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub