Enter data, move automatically to next unprotected cell


G

Guest

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
 
Ad

Advertisements

G

Guest

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.
 
G

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
 
S

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
 
D

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).
 
Ad

Advertisements

G

Guest

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.
 
G

Guest

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 %.
 
S

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
 
S

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
 
Ad

Advertisements

S

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top