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]

Right-click on the Sheet tab name at the bottom of your screen, select View Code, and post this VBA code in the resulting VBA editor window:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "A1" Then
        Select Case Target.Value
            Case "YES"
                Range("B2").Select
            Case "NO"
                Range("B3").Select
        End Select
    End If
End Sub

This should do what you want.

 

[CV899000]

Thank you, I am sure it will.

I have a additional question to this.

I simplified my needs in the first question hoping not to confuse anybody :D
But, do I then have to type in every single value that a drop down can have to get it to move on?
Can I make it move on based on a change on the cell? So if I pick a value in a drop-down menu, that means that the cell has changed, right? Can excel detect that and then pick the next cell?
In some of my drop downs there are a lot of values, so it would be impractical to enter all of them in the code.

 

[Joe4]

Can I make it move on based on a change on the cell?

That is precisely what the code I provided you does. A "Worksheet_Change" event procedure is VBA code that automatically fires when a cell is manually updated on your sheet.

So if I pick a value in a drop-down menu, that means that the cell has changed, right?

Yes, which is why the code I provided works.

I simplified my needs in the first question hoping not to confuse anybody

I always caution people above doing that, as there is a great danger in doing that.
And that is, you get an answer that addresses the exact question you asked, but won't work for you because you oversimplified the question.
It is best to be upfront and straightforward with what you want/need to happen.

Can excel detect that and then pick the next cell?
In some of my drop downs there are a lot of values, so it would be impractical to enter all of them in the code.

Please explain in more details exactly what you need to happen.

 

[CV899000]

You are right, I wont argue with that. :D

Okay, here it goes:

I have an excel workbook, where the sheet "Prices" is where users can input data to calculate a price on some equipment.

This sheet is locked by UserInterFaceOnly.

The thing I want is excel to automatically shift to the next available cell when an entry is completed in the current cell.

Example:
I have B5 where the user is supposed to enter an ID number, such as 111, 222, 333 456, etc, so just a three digit number.
When the user has done that he pressed enter and excel shifts to the next available cell.
The next available cell is B7 which contains a drop-down menu.
When the user has chosen something I that drop-down menu (pressed a given value with the mouse cursor), how do I make excel shift to the next cell automatically without the user having to either move the cursor or press enter?
I want a code that in plain English, detects a change in the drop-down cell and changes to the next available cell.

 

[My Aswer Is This]

If the user enters a value in "B5" you want the active cell to now be "B7" the next available cell according to you. Why would "B6" not be the next available cell.

If not please explain what is "Next available cell"


And this feature will only work in Column "A" correct. starting in row (1)

 

[CV899000]

All cells are as a starting point locked besides from B5 and B7.
When the user choses a value in B7, a cell is unlocked based on this decision, so I basically guide the user through the sheet and only allowing them to enter into specific cells based on their choices in previous cells.
Does this makes sense?
So the next available cell will always just be the next cell that is not locked. So in theory the user could just press enter and move on to the next available cell, that works just fine, but I wan't excel to do it when the user chooses a value/text in a drop-down menu.

 

[CV899000]

I guess I could say that I just want excel to press enter for me when I have chosen a value in any given drop-down menu.
I would assume that I would need excel to detect a change in the cell where the drop-down value is, and when detected simply press enter for me. :D

 

[My Aswer Is This]

Since you did say what columns you want this feature to work on I choose Column "F"

Look at the top of the script and when you see F:F change this to the proper column
This feature only works on column with "Data Validation" list
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F:F")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Target.Offset(1).Select
End If
End Sub

 

[CV899000]

Thank you for the reply.
The reason I did not specify what columns it needs to function on is that there are several.

I have drop downs in columns; B, C, G, H and I.


The code does not work perfectly. It does change the cell, but it chooses a locked cell which it should not do.
Example: If I enter a value into B5 now, and press enter, excel chooses a locked cell which shouldn't be possible.
If I use the drop down menu in cell B7 and choose a value, excel goes on to the next cell, but somehow "unpicks" all cells, so that I again have to press with my cursor on the cell I want.