R
RigasMinho
The following macro lets you take a list in excel: data validation and
lets you store the values you add into the cell.
The only draw back is that you cant delete the cell information from
the top where the forumlar bar is. Whenever you do it repeats it self.
Anyone want to figure out how to change it around so you can delete the
values at the top of the formular bar?
If you look at the code you'll notice that it works when you have data
validation in columns 3 - 4-5. just add an or command for more colmns.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
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 = 5 Or Target.Column = 3 Or Target.Column = 4 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
lets you store the values you add into the cell.
The only draw back is that you cant delete the cell information from
the top where the forumlar bar is. Whenever you do it repeats it self.
Anyone want to figure out how to change it around so you can delete the
values at the top of the formular bar?
If you look at the code you'll notice that it works when you have data
validation in columns 3 - 4-5. just add an or command for more colmns.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
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 = 5 Or Target.Column = 3 Or Target.Column = 4 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub