Excel VBA, move mouse to different cell

Question

In Col A, if A3 = No, the mouse cursor moves to F3, that is fine. In Col A, if A4 = No, the mouse cursor moves to F4 and so forth.

I can't seem to figure out the correct code for or how to write the code correctly.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) <> "A3" Then Exit Sub
Application.EnableEvents = False
If Target.Value = "No" Then
    Range("F3").Select
End If
Application.EnableEvents = True
End Sub

That's what I got to make it work for one cell so far.

Set your desired range to a variable (guessing column A) and intersect that with your target? — findwindow
– findwindow, Commented May 13, 2022 at 15:06
Could you be more accurate? When exactly should what be selected? Your text says Yes, while your code says No. — VBasic2008
– VBasic2008, Commented May 13, 2022 at 15:09
Ah sorry, yes, meant to say "No".. each new row is a new data entry, A3 could be No, A4 could be No, i need the No to move to Col F on the same row — halo_4
– halo_4, Commented May 13, 2022 at 15:10

BigBen · Accepted Answer · 2022-05-13 15:39:03Z

3

Check if Target is in column A, if so move to the same row in column F:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
    If Target.CountLarge > 1 Then Exit Sub

    If Target.Value = "No" Then
       On Error GoTo SafeExit
       Application.EnableEvents = False
       Me.Range("F" & Target.Row).Select
    End If

SafeExit:
    Application.EnableEvents = True
End Sub

Note that .Select-ing doesn't cause the change event to refire, so you only need to disable events if you have a SelectionChange event handler you don't want to be executed.

edited May 13, 2022 at 15:39

answered May 13, 2022 at 15:22

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

VBasic2008 · Accepted Answer · 2022-05-13 15:25:33Z

A Worksheet Change: Offset Conditionally

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const FirstCellAddress As String = "A3"
    Const ColumnOffset As Long = 5
    Const Criteria As String = "No"
 
    Dim irg As Range
    With Range(FirstCellAddress)
        Set irg = Intersect(.Resize(Rows.Count - .Row + 1), Target)
    End With
    
    If irg Is Nothing Then Exit Sub

    Dim arg As Range, cCell As Range
    Dim a As Long, c As Long
    
    For a = irg.Areas.Count To 1 Step -1
        Set arg = irg.Areas(a)
        For c = arg.Cells.Count To 1 Step -1
            Set cCell = arg.Cells(c)
            If StrComp(CStr(cCell.Value), Criteria, vbTextCompare) = 0 Then
                cCell.Offset(, ColumnOffset).Select
                Exit For
            End If
        Next c
    Next a
    
End Sub

Collectives™ on Stack Overflow

Excel VBA, move mouse to different cell

2 Answers 2

Comments

A Worksheet Change: Offset Conditionally

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

A Worksheet Change: Offset Conditionally

Comments

Your Answer

Sign up or log in

Post as a guest

Related