CODEWITHSUNDEEP
excelvba
halo_4
halo_4

Reputation: 63

Excel VBA, move mouse to different cell

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.

Upvotes: 2

Views: 1094

Answers (2)

BigBen
BigBen

Reputation: 50008

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.

Upvotes: 3

VBasic2008
VBasic2008

Reputation: 54807

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

Upvotes: 2

Related Questions