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.

 

[Joe4]

I see that you deleted and re-posted the conditions that I worked my last reply off of.
See if this does what you are looking for:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim os As Long
    Dim cnt As Long

    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
        Select Case Target.Value
'           If "YES", move down to next unlocked cell
            Case "YES"
                os = 1
                Do Until os = 100
                    If Target.Offset(os).Locked = False Then
                        Target.Offset(os).Select
                        Exit Do
                    Else
                        os = os + 1
                    End If
                Loop
'           If "NO", move down to third unlocked cell
            Case "NO"
                os = 1
                cnt = 0
                Do Until os = 100
                    If Target.Offset(os).Locked = False Then
                        cnt = cnt + 1
                        If cnt = 3 Then
                            Target.Offset(os).Select
                            Exit Do
                        Else
                            os = os + 1
                        End If
                    Else
                        os = os + 1
                    End If
                Loop
        End Select
    End If

End Sub

 

[CV899000]

Thank you very much.

Yes, you are right. I wanted to make an insertion in the text, but the tab button on the keyboard apparently made me post what I has done and I wasn't finished
Then I couldn't edit the post, so I made a new one and deleted the old, but by then you had apparently already seen my half finished post

But I will try this code a little later, its dinner time I Denmark, so later on tonight, I'll give it a try! Thanks for the quick replies!

 

[CV899000]

It only seems to be working for cell B7 this code.
It jumps to the next unlocked cell when changing anything in B7, but it doesn't do it with the rest of the cells.

 

[Joe4]

It jumps to the next unlocked cell when changing anything in B7, but it doesn't do it with the rest of the cells.

It works fine for me. I have cells B20 and B21 locked and protected.
When I select "YES" in B19, it correctly jumps down to B22.

If it is not working, please provide an example where it is not working.
That is, tell us what you are updating in what cell, and what cell it should be jumping down to (and explain why, mentioning which cells are locked and protected).

 

[CV899000]

As I wrote, it does that for me also.

If I choose "YES" in cell "B7" then I jumps to "B8" correct.

If I then choose a value in "B8", then it does not jump to "B9" as it should.

 

[Joe4]

If I then choose a value in "B8", then it does not jump to "B9" as it should.

It does for me.
Is B9 locked/protected, merged, or hidden?
Did you do a Copy/Paste of the code I provided above, exactly "as-is", or did you type it or modify it?

 

[CV899000]

The only part I changed was as follows:

 If "NO", move down to third unlocked cell
            Case "NO"
                os = 1
                cnt = 0
                Do Until os = 100
                    If Target.Offset(os).Locked = False Then
                        cnt = cnt + 1
                        If cnt = 3 Then [COLOR=#FF0000][SIZE=2](I CHANGED 3 to 1)[/SIZE][/COLOR]
                            Target.Offset(os).Select
                            Exit Do

B9 is not locked, protected or hidden. I can choose it fine using either enter or my mouse.

 

[Joe4]

Why did you change that from 3 to 1?
I thought you wanted to move down three cells if they select "NO".
What are your exact conditions?
Should there be any difference in how many to jump depending on picking "YES" or "NO"?

Also, do the case of your selections match your code?
In the cells, are the options "YES" and "NO" or "Yes" and "No"?

 

[CV899000]

Yes, I did, but for some reason and please do not ask me why, but it moved 5 down instead. Maybe it is because that if they choose "NO" in B7 then B8 and B9 is locked, and the cursor should move to B10 (3 actual cells, but only 1 active cell down).

The options is "YES" and "NO" but only in B7.
In B8 to give an example, the options is "NSL", "ESL" or "DSL"
I cannot write all options in all drop-down menus, there are simply to many

If excel could just move to the next cell that is not locked everytime, then it would be perfect, I can lock and unlock the cells easily.

 

[Joe4]

In B8 to give an example, the options is "NSL", "ESL" or "DSL"

Well, that explains it! You had originally set the conditions as "YES" and "NO", so those were the only two conditions it was checking for.
These minor details are VERY important. We are writing the code based on what you are telling us.

If you do not care what the values selection are, and just want it to move down to the next active cell in that column, then we can simplify the code greatly, like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim os As Long
    Dim cnt As Long

    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
        os = 1
        Do Until os = 100
            If Target.Offset(os).Locked = False Then
                Target.Offset(os).Select
                Exit Do
            Else
                os = os + 1
            End If
        Loop
    End If

End Sub