I lose my tab order

[ScratchingMyHead]

I have a worksheet that I'm using like a form. I have set the tab order with a sub...

Please Login or Register  to view this content.

The cells in the sub have been manually unlocked. These are the cells I wish to tab or enter through in order. The worksheet is unprotected. Everything works like it should except if I manually click on any cell including the ones in the sub, I lose the tab order.

If I protect the sheet and then Activate it, I lose the tab order although those unlocked cells are all I can access.

How can I select any of the unlocked cells without losing my tab order?

[NeedForExcel]

What exactly do you mean by Losing tab Order? Could you clarify more?

Deep

[ScratchingMyHead]

Thank you for your quick response.

In my sub I have set the tab order so that sometimes it moves from right to left and sometimes it tabs down several cell in sequential order. For example, I may have 3 cells in 1 column (A1,A2,A3) to enter someone's address, when I tab or enter it moves down in that order. If I manually click on A2, hitting tab or enter moves the focus to another unlocked cell that is in the same row i.e. C2. Although it will only tab through the unlocked cells, the order I originally setup is lost.

[ScratchingMyHead]

Anyone? Let me know if I'm not explaining this properly.

I want the focus to move from chosen cell to chosen cell when I either use the Tab or Enter key. I have created the cell to cell order in the above sub (If I haven't or it doesn't make sense let me know). It works fine except if I manually take my mouse and click into any of the cells on the spreadsheet I lose that order. By doing that it will scroll through all the unlocked cells but in a left to right reading order i.e. left > right and then down 1 row.

I would like to know how I can maintain the movement of the focus from cell to cell when using the Tab or Enter key. I do not want to lose my programmed cell to cell order. Do I need to programmatically code something so when a locked cell is accessed the programmed order is reset?

[millz]

Do you want to limit to only able to select those cells all the time?

Try changing the event to this and see if that's what you want?

Please Login or Register  to view this content.

Edit: added second line to allow selection by clicking on the cells
Edit2: check if only a single cell is selected...

[ScratchingMyHead]

Do you want to limit to only able to select those cells all the time?

Try changing the event to this and see if that's what you want?

Edit: added second line to allow selection by clicking on the cells

Thank you. To answer your first question... Yes.

What I have is a form and only the cells mentioned are ones that need to be accessed. Your sub works great. Is there something we can code so that the sub does not fire when one of the unlocked cells in the range is selected with a mouse click but the order remains?

[millz]

Is there something we can code so that the sub does not fire when one of the unlocked cells in the range is selected with a mouse click but the order remains?

Not exactly sure if you are talking about a whole new thing or the second line of code I've added to the previous post?

[ScratchingMyHead]

The first solution is fine. Thank you so much for the help. I use to do a fair amount of VBA stuff in Access several years ago so if I'm pointed in the right direction I can usually work things out. This one was giving me some problems though.

[ScratchingMyHead]

After a bit of testing.....ONE HUGE PROBLEM

Everything appeared to be working GREAT. If I delete the contents of a cell it deletes the contents of all the cells and I have a blank form. Not good.

[millz]

Yeah, that's the nature of Excel, pressing delete key will clear contents on all selected cells. You can clear only the active cell with backspace.

[ScratchingMyHead]

Thank you very much for all your help and information.