automatically advance to next cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to limit a cell to only one character then automatically
advance to the next cell after a single character is entered.
 
No

Excel has no way of knowing you are finished editing a cell until you leave that
cell by enter, arrow or tab.


Gord Dibben MS Excel MVP
 
But you could create a userform that allows the user to only enter a single
character. The goes to the next cell.

If you want to try, is there a list of specific characters that can be used?

And what does "next cell" mean?

The one to the right of the activecell???
The one beneath the activecell???
 
Dave
It could be any letter or number and by next cell I mean the cell to the
right of the active cell. Thanks for your quick reply
 
Modified from a previous post:

Create a small userform with a single textbox on it. Use the X button to close
the userform.

Put this code in a General module show the userform:

Option Explicit
Sub testme01()
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
'only 0-9, a-z, and A-Z
Case Asc("0") To Asc("9"), Asc("a") To Asc("z"), Asc("A") To Asc("Z")
With ActiveCell
.Value = Chr(KeyAscii)
'if you want to fill A:E, you could use
'If ActiveCell.Column = 5 then '5 is column E
'or
'use this for all columns.
If ActiveCell.Column = Columns.Count 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
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
 
Back
Top