Move cursor based on current location of cursor

Question

I created a series of questions. Depending on the answer, VBA code will unhide/hide rows if additional information is required.

For instance, for the question is "Do you own a home?"

If the user selects Yes, the code will unhide the next row and will require them to fill out their address.
If the user selects No, the code will keep the the Address row hidden because it is not necessary that the user fill anything out.

The next step I want to implement is to focus the cursor on the next row to help the user in navigating the form. So in the previous example, if the user selects Yes, I'd like to implement VBA code that will put the cursor on the next row using code similar to this:

If UCase(Range("I2").Value) = "NO" Or IsEmpty(Range("I2").Value) Then
    Rows("3:3").EntireRow.Hidden = True
Else: Rows("3:3").EntireRow.Hidden = False
    Range("I3").Select
End If

If UCase(Range("I6").Value) = "NO" Or IsEmpty(Range("I6").Value) Then
    Rows("7:7").EntireRow.Hidden = True
Else: Rows("7:7").EntireRow.Hidden = False
    Range("I7").Select
End If

There is about 100 questions, and though this method works for one question/answer, the code gets messed up because on later questions (like in the example above), the VBA code keeps on selecting cell I3 because cell I2 remains as "Yes".

Rather than using the .Select method, is there another way so the VBA code will move the cursor to the next appropriate cell despite previous entries?

Read this https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — SJR, May 13 '19 at 16:41
Assuming your are using a change event, you want the event to look at the cell that was just modifies by the user and only perform actions based on that — cybernetic.nomad, May 13 '19 at 16:44
@SJR I think this is one of the few cases where Select is being used for its intended purpose, and that link doesn't apply — Josh Eller, May 13 '19 at 18:55

Peicong Chen · Answer 1 · 2019-05-13T19:34:07.450

There is nothing wrong with using .Select, the unexpected result is caused by your logic on when to use .Select. You need additional logic to skip the .select. you can try the below code.

If UCase(Range("I2").Value) = "NO" Or IsEmpty(Range("I2").Value) Then
    Rows("3:3").EntireRow.Hidden = True
ElseIf UCase(Range("I2").Value) = "YES" And IsEmpty(Range("I3").Value) Then
    Rows("3:3").EntireRow.Hidden = False
    Range("I3").Select
Else
    Rows("3:3").EntireRow.Hidden = False
End If

I added the below code if your question and answer line follows a specified pattern: The cell right below your "Yes/No" cells is the value requires input.

Dim ArryRng       As Variant

For Each ArryRng In Array(Range("I2"), Range("I6")) 'Set your Yes/No range

    If UCase(ArryRng.Value) = "NO" Or IsEmpty(ArryRng.Value) Then
        Rows(ArryRng.Row + 1).EntireRow.Hidden = True
    ElseIf UCase(ArryRng.Value) = "YES" And IsEmpty(ArryRng.Offset(1, 0).Value) Then
        Rows(ArryRng.Row + 1).EntireRow.Hidden = False
        ArryRng.Offset(1, 0).Select
    Else
        Rows(ArryRng.Row + 1).EntireRow.Hidden = False
    End If
Next

I'm still encountering the same issue. Keep in mind, there are about 100 questions on my sheet, so this code will need to be duplicated 100 times. If cell I2 = "Yes", the remaining 99 questions keep on selecting cell I3... — ranopano, May 13 '19 at 19:17
If Cell I2 = "YES" and cell I3 is not empty then I3 will not be selected. And if you have 100 questions, you may have to make 100 of such logic variations, unless you have specific rules on location of each questions and answer row. — Peicong Chen, May 13 '19 at 19:25

Move cursor based on current location of cursor

1 Answers1