Enter data, move automatically to next unprotected cell

[Patrick Riley]

In a protected worksheet, I want to enter a single digit in an unprotected
cell, then have the cursor move automatically to the next unprotected cell.
How can I do this?
---Patrick Riley

 

[Barb Reinhardt]

I guess I'm confused. If you've deselected the "LOCKED" value on the
protection tab of the format cells command and then protected the sheet, it
should do exactly what you want.

 

[Gord Dibben]

Barb

Patrick wants to move to next cell without hitting Tab, ENTER or Arrow key.

i.e. type a digit and have Excel jump to next cell with no futher action from
user.


Gord Dibben MS Excel MVP

 

[Barb Reinhardt]

I see. Is there a way to do that? I'm not aware of one.

 

[Susan]

patrick -
then how in the world would excel know you are FINISHED typing?
is it jumping after 1 character is entered? 2? 3? 10?
if you want it after just one character, i guess you could do it with
a worksheet_change macro.
susan

 

[Dorak]

Barb

Patrick wants to move to next cell without hitting Tab, ENTER or Arrow key.

i.e. type a digit and have Excel jump to next cell with no futher action from
user.

Gord Dibben MS Excel MVP




- Show quoted text -

Column width sets how many characters the cell can contain. So format
the width of the column to 1. Then just type your number and it will
automatically move to the next cell (either up or down, however you
choose excel to react).

 

[Patrick Riley]

Gord:
Thank you for more clearly articulating my request. Yes, I wanted the move
to the next cell to occur without hitting TAB, ENTER, or arrow key.
As you (responding to a slightly-differently worded query) and Susan have
now stated, Excel could not know when I had finished data entry for a cell,
thus would have no clue as to when to exit.
Thanx to all who responded.

 

[Patrick Riley]

Dorak:
I set column-width to 1, but still got no automatic advancement to the next
(unprotected) cell after typing in a digit. Also, the typed-in digit was not
visible on-screen, even when I set Zoom to 300 %.

 

[Susan]

dorak -
i set my column width to 1 & tried this.
with both text & numbers, it doesn't automatically move to the next
cell - it just sits there while you type in more (which you can't
see).
do you have something turned on that i don't have turned on that would
make this happen?
susan

 

[Susan]

i tried 2 other ways:
a macro:

Sub Worksheet_Change(ByVal Target As Range)

If ActiveCell.Characters.Count = 1 Then
ActiveCell.Offset(1, 0).Select
End If

End Sub

it doesn't work because the characters.count doesn't happen until
AFTER you exit the cell - before you exit the cell, excel doesn't know
there's anything there. it's empty. the only way to exit the cell is
by hitting enter or an arrow.

i also tried validation & locked a range of cells so that the input
had to be <10 (thereby being a single-digit number), but again, that
doesn't trigger until you exit the cell.

i am going to look & see if there is a keystroke_click event.......
susan

 

[Susan]

just for general info - in other threads in the programming group, i
found the following information:

"VBA doesn't run when the sheet is in "edit" mode."
"worksheet_change only fires when you're not in edit mode, so there is
no event fired on keystrokes. It only fires when you exit the cell
after editing the value."
http://groups.google.com/group/micr...e+change+event&rnum=10&hl=en#468926701f458355

"Clicking a cell does not fire the change event. Editing a cell does
(which you do by clicking in the formula and hiting enter). I suspect
you are mistaken that this fires when you click in a cell. "
http://groups.google.com/group/micr...t+change+event&rnum=10&hl=en#8cf0057f346e9b38

so there's no change event that will work automatically without
exiting the edit-mode of the cell (by using tab, enter, or an arrow).
susan