I'm using the below macro to allow my data validation fields to handle multiple choices from the drop-down, which works. However, when I go to a cell where I can just key in data such as Phone Number, and I press enter, it does not move down like it normally would when you press enter. It just jumps back to the cell you just worked on.
If you were to copy this code to a blank workbook. Unlock a few cells like A1 through A10. Then create a short range for your data validation to the side of column A. Create a data validation list dropdown in cell A1. Then try to key something random in the blank cell of A2 and press enter, it just bounces back to A2 instead of going down to cell A3...
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String
Dim rn As Range
If Target.Count > 1 Then Exit Sub
Sheets("Form").Unprotect Password:="123"
On Error Resume Next
Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then Exit Sub
Application.EnableEvents = False
If Not Application.Intersect(Target, xRng) Is Nothing Then
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 <> "" Then
If xValue2 <> "" Then
Target.Value = xValue1 & ", " & xValue2
End If
End If
End If
Set rn = Target
rn.EntireRow.AutoFit
rn.Select
Sheets("Form").Protect Password:="123"
Application.EnableEvents = True
End Sub
Bookmarks