Automatically move to next available cell after drop-down menu input

[CV899000]

Hi,

I have a sheet where I have several drop-down menus.

I would like to know if I can get a VBA code that shifts the active cell as soon as a value have been chosen in a drop-down menu.

Example:

Lets say that cell A1 has a drop-down menu containing "YES" and "NO".
Depending on my choice, the next available cell (the other cells will be locked) will be either B2 if "YES" or B3 if "NO"

and so on with my other drop downs also.


But how do I make excel change to the next cell automatically when I choose one of the answers so that I do not have to move my cursor or press enter to move to next cell.

 

[My Aswer Is This]

Not sure what your saying. I tested this on a column with a Data validation list and it worked for me. It will not work on other columns because the cell value does not change till you press enter of arrow down key.
And not sure why you would have locked cells in a column with a drop down list.

I know of no way to tell the script to jump to the next unlocked cell.

And I could show you how to include those other columns but no need if this is not doing what you want.

 

[CV899000]

So, I have decided that I only need this function on column B,
and I have made the code work as it should in my sheet, but I am almost 100 % sure that it could have been done in a much much smarter way.

Here is my code, please excuse the rudimentary coding, I am not used to this.

Private Sub Worksheet_Change(ByVal Target As Range)
If Worksheets("Prices").Range("B7").Value = "YES" Then
If Not Intersect(Target, Range("B7,B8")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Target.Offset(1).Select
End If
If Not Intersect(Target, Range("B19,B23,B24,B25")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Target.Offset(1).Select
End If
End If
If Not Intersect(Target, Range("B9")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Target.Offset(3).Select
End If
If Worksheets("Prices").Range("B7").Value = "NO" Then
If Not Intersect(Target, Range("B7")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Target.Offset(3).Select
End If
If Not Intersect(Target, Range("B19,B23,B24,B25")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Target.Offset(1).Select
End If
End If
End Sub

 

[CV899000]

Okay, so I said this was working, and it is.... However, it now gives me a Method Of Range Class Failed error due to all the .selects I have done....
Can anybody help with that?

 

[Joe4]

I really do not like to look at unformatted VBA code and try to guess what the user is intending to do.
So can you please explain in detail (in plan English) exactly what you want to happen, in regards to column B?

 

[CV899000]

Sorry for that.

Yes I can try.

I want excel to "push the enter key for me" when I select something in my drop down menus so that it moves the cursor to the next unlocked cell.
What I have done in the code is to manually say that it should move to a specific cell if the value in my drop down menus change.

 

[Joe4]

No, I am talking about the specifics of your code. It looks maybe you want some to jump 1 cells in certain conditions, and others to jump 3 cells in other conditions.
So, please list out the different cells you want to be affected by this, and what conditions are attached to each one.

I also locked your other thread on the same topic. Please do not start new topics on the same question (that you have already posted here). That is a good way to upset the people who are trying to help you.

 

[CV899000]

Deleted

 

[CV899000]

This thread kind of developed into this problem, but the question I created this post for was answered, so that is why I created a new post, to try and keep it separated, but sorry if it was wrong.

Okay, I'll explain the meaning of the code.
I'll try doing it in sections so I can refer to it in my description.

Cell B7 is a drop-down menu containing two possible outcomes: "YES" and "NO".
1. If I choose "YES" in cell B7, then cell B8 is unlocked and I want excel to automatically move the cursor to B8 (without me pressing enter), hence going 1 cell down.

2. Cell B8 also contains a drop down menu, with multiple choices. If Cell B8 changes (indicating that I have selected something from the drop down menu) then cell B9 is unlocked and I want excel to automatically move the cursor to B9 (without me pressing enter), hence going 1 cell down.

3. The same principle with B19,B23,B24,B25.

4. Back up to section 2. I am now in cell B9 which also contains a drop down menu containing multiple choices. Because that I chose "YES" in section 1, cells B10 and B11 is locked and therefore when I chose a value in B9, I want the cursor to drop to cell B12, hence going 3 cells down.

5. If I choose "NO" in cell B7 instead of "YES" as stated in section 1, then cell B8 and B9 is locked and I want the cursor to move to B10, hence going 3 cells down.

6. The same principle with B19,B23,B24,B25.

 

[Joe4]

This thread kind of developed into this problem, but the question I created this post for was answered, so that is why I created a new post, to try and keep it separated, but sorry if it was wrong.

You just have to choose one or the other (post your new question to the current thread OR start a new thread), but not do both.
The issue with doing both is the same reason/problems Cross-Posting causes. See this for a detailed explanation on that: Excelguru Help Site - A message to forum cross posters

If I choose "YES" in cell B7, then cell B8 is unlocked and I want excel to automatically move the cursor to B8 (without me pressing enter), hence going 1 cell down.
Cell B8 also contains a drop down menu, with multiple choices. If Cell B8 changes (indicating that I have selected something from the drop down menu) then cell B9 is unlocked and I want excel to automatically move the cursor to B9 (without me pressing enter), hence going 1 cell down.
The same principle with B19,B23,B24,B25.

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

    If Not Intersect(Target, Range("B7,B8,B19,B23,B24,B25")) Is Nothing Then
        If Target = "YES" Then Target.Offset(1).Select
    End If

End Sub

 

[CV899000]

Thank you, I tried that already, but that goes wrong if I enter "NO" or the times when I need it to skip down 3 cells for example.

I was kind of hoping that I could avoid the "Target.Offset(1).Select" bit of code and use something that in plain English logic would be "go to next unlocked cell"