Pointers??

[OzziJC]

G'day,
this q is also posted elsewhere, but all help needed...I am trying
to set a pointer so that when data is added to a blank cell the next
blank cell is selected. (not nesscarily the next cell in turn)

Thanx

JC

 

[DM Unseen]

If you mean data entry flow, you can archive that with:

locking all cells that are not data entry,
protecting the sheet
now you can TAB through all unlocked cells.

BTW Excel tabs from left to right and then down.

Note that while actually entering something in a cell, ALL OTHER
ACTIONS ARE SUSPENDED. So actually doing something while editing in a
cell is impossoble in XL.

DM Unseen

 

[William Benson]

Hi, try this in the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.End(xlDown).Offset(1, 0).Select
End Sub

 

[OzziJC]

Have already locked sheet it is in the TAB direction i am trying to
change. ie some times i need to be able to press TAB and move down,
other times i can tab to right. Thought there might by somethging to
direct tab function to next cell required rather than default
setting..

Thanx

JC

 

[Tom Ogilvy]

I doubt that there are any settings that will tab left sometimes and
othertimes tab down without you specifying when the change should be made.
You might select the cells in the order you want to tab to them, then if you
tab within the selection, the order will be maintained.

 

[William Benson]

JC,

What was wrong with my solution, and using enter or arrow key? I tested it
and it worked. Was there some background to this which expanded on the
circumstances, but appeared in a different post?

Thanks for shedding light.

Bill

 

[DM Unseen]

Bill,

I think your solution implied the next cell to change is 1 below the
edited cell.

That said, your soluition can be edited to help Ozzi with his quest:

Ozzi,

You cannot tell excel for Each Cell where to go to next (as Tom
Stated).

Using Tom's idea you could write something like

Range("A1,C5,B9,A2").Select

After this TAB will follow your cell order (so first A1 then C5).

Bill's solution would be something like:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strNewCell as string
Select case Target.Address
case "A1" strNewCell = "C5"
case "C5" strNewCell = "B9"
end select

Range(strNewCell).Select

End Sub

Note this way has more possibilities, but needs more code

DM Unseen

 

[William Benson]

not so ... although I did pick one direction (down) I do go past every
non-empty cell on the way down to the next blank cell. Thanks for hanging
with this DM (do you go by another name ? , I am learning the ropes
here...

Bill

if you

 

[DM Unseen]

Bill,
sorry you're right your code skips non emtpy cells downwards.

Stll, I suspect Ozzi looks for a TAB order in cell editing

DM Unseen AKA M. Evers (I just love my nick, and since I use google I
have no sig so I keep it short)