Hi, I'm coming back to VBA after a little while and have forgotten most things it appears.
I have this macro that affects cells within a range once I input certain data. If I input YL or YE, it adds a number entered into a message box in the same row on column AK. If a C is entered into a cell in the range, it allows me to add a comment to that cell via a message box. Finally, it allows me to include multiple options from one data validated dropdown list into the same cell.
I wasn't having any issues until I added the last element to the macro - including multiple dropdown list elements (I took it from an old spreadsheet I had created back in 2017). I'm now finding that after adding data into the cell and pressing enter, the active cell briefly drops to the one below, and then returns to the one I had previously added data to. This isn't great as it means every time I need to go to the cell below I have to press enter twice. I'm really unsure as to why and was hoping someone could help!
Thanks!
I have this macro that affects cells within a range once I input certain data. If I input YL or YE, it adds a number entered into a message box in the same row on column AK. If a C is entered into a cell in the range, it allows me to add a comment to that cell via a message box. Finally, it allows me to include multiple options from one data validated dropdown list into the same cell.
I wasn't having any issues until I added the last element to the macro - including multiple dropdown list elements (I took it from an old spreadsheet I had created back in 2017). I'm now finding that after adding data into the cell and pressing enter, the active cell briefly drops to the one below, and then returns to the one I had previously added data to. This isn't great as it means every time I need to go to the cell below I have to press enter twice. I'm really unsure as to why and was hoping someone could help!
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errH
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim strVal As String
Dim i As Long
Dim lCount As Long
Dim Ar As Variant
On Error Resume Next
Dim lType As Long
lType = Target.Validation.Type
If lType = 3 Then
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Row >= 13 _
And Target.Row <= 35 _
And Target.Column = 13 _
Or Target.Column = 15 _
Or Target.Column = 17 _
Or Target.Column = 19 _
Or Target.Column = 21 _
Or Target.Column = 23 _
Or Target.Column = 25 _
Or Target.Column = 27 _
Or Target.Column = 29 _
Or Target.Column = 31 _
Or Target.Column = 33 _
Or Target.Column = 35 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
On Error Resume Next
Ar = Split(oldVal, ", ")
strVal = ""
For i = LBound(Ar) To UBound(Ar)
Debug.Print strVal
Debug.Print CStr(Ar(i))
If newVal = CStr(Ar(i)) Then
'do not include this item
strVal = strVal
lCount = 1
Else
strVal = strVal & CStr(Ar(i)) & ", "
End If
Next i
If lCount > 0 Then
Target.Value = Left(strVal, Len(strVal) - 2)
Else
Target.Value = strVal & newVal
End If
End If
End If
End If
End If
Dim cell As Range
If Not Intersect(Target, Range("L3:AJ33")) Is Nothing Then
Application.EnableEvents = False
For Each cell In Target
If cell.Value = "YL" Then
Dim number As Variant
Dim evalCell As Range
number = Application.InputBox(Prompt:="How many minutes late did the student arrive?", Type:=1)
Set evalCell = Range("AK" & Target.Row)
If IsNumeric(evalCell.Value) And IsNumeric(number) Then
evalCell.Value = evalCell.Value + number
End If
ElseIf cell.Value = "YE" Then
number = Application.InputBox(Prompt:="How many minutes early did the student leave?", Type:=1)
Set evalCell = Range("AK" & Target.Row)
If IsNumeric(evalCell.Value) And IsNumeric(number) Then
evalCell.Value = evalCell.Value + number
End If
ElseIf InStr(Target, "C") Then
Dim ans As String, oComment, Cmnt As String
Cmnt = InputBox("Please enter a comment about the student")
With Target
If .Comment Is Nothing Then
.NoteText Cmnt
Else
ans = MsgBox("Yes = Add to comment" & Chr(10) & "No = Replace old comment with new comment ", vbYesNo + vbInformation)
If ans = vbYes Then
oComment = .Comment.Text
.NoteText oComment & Chr(10) & Cmnt
ElseIf ans = vbNo Then
.NoteText Cmnt
End If
End If
End With
Target.Comment.Visible = False
End If
Next
errH:
If Err.number <> 0 Then MsgBox Err.number & " " & Err.Description
Application.EnableEvents = True
End If
End Sub
Thanks!