VBA to move between unlocked cells in specific order

Hi

I have a sheet where I want the user to be guided to the next cell in sequence on a protected sheet, to either type in an answer or make a selection from a drop down list, or leave blank as the case be, before moving on to the next specified cell.

I'm currently using a worksheet change event to move to a particular cell if the current cell contains an entry, the problem is if a user leaves a cell blank and then moves on it goes back to the blank cell, as I also have Data Validation messages in each cell, as the macro runs and checks each cell for an entry you see the validation message pop for a split second which doesn't look good

This is a section of my existing code

Please Login or Register  to view this content.

Is there an alternative simple code that would guide the user to cells in a specific order, and not go back looking for empty cells if the user left it blank for some reason which they may need to do? I could get round leaving the cell blank by asking the user to select N/A if appropriate so the curser goes on to the next cell, however the code still appears to go back and check each cell looking for an entry where you see the data validation message box flash very briefly

So if I have a range of cells for example in order of G5,J5,O5,G7,J7,G8,G9 etc that I want the user to be guided to, how could I get the next cell in sequence to be selected if the user presses Enter after typing something in the cell, makes a selection from a drop down list within the cell, or leaves it blank and presses enter?

Hope that isn't too confusing

Many thanks in advance

Twaddy

Hi Mehmetcik

Thanks very much for taking the time to look at this and post your solution which I have working ok, really appreciate it

Something I've realised which is a small issue is that if a user wants to go back and change of one their answers once they've ran through the full set of sequential cells, say they want to change the 3rd cell in sequence, they can select the cell ok to change their answer however if they also want to then change the 7th cell in sequence they have to re-input their answers in the 4th, 5th and 6th cells to get to the 7th. Is there a way to stop the code once it been completed once and reached the last cell to allow the user to easily go back and change answers?

Many thanks

Twaddy

Apologies however what would the code look like and where would I enter it

When you say you can just select any cell, do you mean with the code as it stands on your original post? I've found that you have to make an entry in each cell in sequence otherwise it keeps going back to the blank cell. Although saying that I've noticed on a couple of issues tonight

Some of the cells require a selection from a drop down list, if you type an invalid entry in the cell rather than using the drop down list you get the Validation error message with options to Retry Cancel or Help, if you press Retry you can't use the drop down arrow any longer, if you press cancel it deletes the incorrect entry and moves the cursor to the next cell and you can't go back. I've also found that if you double click in a cell the cursor appears in the next blank cell in sequence, where you can either make an entry or if you press enter it moves to the next cell

Apologies for coming back with more issues, hopefully you can help

Many thanks

Twaddy

Sample.xlsm

Hi

Hopefully that's uploaded ok and gives you what you need, I've mocked up the path I need the cursor to take, filled the cells yellow and numberer the desired path starting at 7, you'll see the first 6 yellow cells at the start of the sheet which I haven't numbered as some will have validation lists which I've left in so you can see the issue if you try and enter something not on the list, ideally I'd like the top 6 cells to run across then down and across again before moving on to ref 7

I'd added your code, which does work if you enter everything as you should, unfortunately I know there are lots of people at work who struggle with anything Excel based hence I'm trying to make it foolproof. The issues are the drop downs as I mentioned, another being if you double click into a cell it moves on and you can't go back, and then if you run through the full sequence to the end and then want to change some answers. Say you want to change ref 7, you can click on that cell fine, if you then want to also change say ref 15 you can't unless you enter answers 8 through to 15 again

Thank you so much for your help with this

Twaddy