Move to next unprotected cell automatically after entering data

[Patrick Riley]

I have a protected worksheet. I enter a single digit of data into
unprotected cells. I want the cursor to automatically move to the next
unprotected cell after I input the single digit, without the need to use the
TAB or ENTER key.
Does anyone have a solution?
Patrick Riley

 

[Gord Dibben]

Patrick

Excel has no way of knowing when you have completed data entry in a cell without
some sort of trigger like Tab, ENTER or Arrow key to move out of the cell.

No code can run while you are in Edit mode so VBA is out.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Without knowing what "the next unprotected cell" means...

Saved from a previous post:

I would turn
tools|options|edit tab|move selection after enter
to down or right or ...

Then turn number lock on and use the numeric keypad to type your digit

Hitting the enter key on the numeric keypad doesn't seem too bad to me.

Another alternative is to create a tiny userform that just looks for a digit:

Put a single textbox on it (use the X button to close the userform).

Put this code in a General module:

Option Explicit
Sub testme01()
Cells(ActiveCell.Row, 1).Activate
UserForm1.Show
End Sub

Add this code to the userform module:

Option Explicit
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Select Case KeyAscii
Case 48-57 'Numbers 0-9
With ActiveCell
.Value = Chr(KeyAscii)
'A:E, then down a row
if activecell.column = 5 then
activecell.entirerow.cells(1).offset(1,0).activate
else
.Offset(0, 1).Activate
end if
End With
End Select
KeyAscii = 0
TextBox1.Value = ""

End Sub

This code goes from A:E then next row, column A.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish has some getstarted instructions for userforms at:
http://contextures.com/xlUserForm01.html