Pointers??

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

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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> OzziJC
> ------------------------------------------------------------------------
> OzziJC's Profile:
> http://www.excelforum.com/member.php...o&userid=24517
> View this thread: http://www.excelforum.com/showthread...hreadid=381132
>

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

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.

--
Regards,
Tom Ogilvy

"OzziJC" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> OzziJC
> ------------------------------------------------------------------------
> OzziJC's Profile:
http://www.excelforum.com/member.php...o&userid=24517
> View this thread: http://www.excelforum.com/showthread...hreadid=381132
>

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


"OzziJC" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> OzziJC
> ------------------------------------------------------------------------
> OzziJC's Profile:
> http://www.excelforum.com/member.php...o&userid=24517
> View this thread: http://www.excelforum.com/showthread...hreadid=381132
>

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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)