Cell jumping from active cell to the one above after macro

Rxm177

New Member
Joined
Apr 20, 2022
Messages
1
Office Version
  1. 365
Platform
  1. MacOS
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!

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!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Forum statistics

Threads
1,214,861
Messages
6,121,973
Members
449,059
Latest member
oculus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top