Data entry

  • Thread starter Thread starter Allen
  • Start date Start date
A

Allen

Can I have Excel, when entering data into a cell tab to
the next cell automatically.
Ex. Say I have a cell that I want to take only 4 numbers.
When I enter the 4th digit, can excel jump to the next
cell without me using the tab or enter keys?
 
Allen,

Unfortunately, that can't be done.
Macro code doesn't run while a cell is in edit mode.

You might want to look into using a custom UserForm
or even a control (e.g. TextBox) on the worksheet where
you can trap events and count characters.

An example...using a TextBox from the Controls Toolbox
named "TextBox1".
Right click on the worksheet tab, select View Code and
copy and paste the following:

Private Sub TextBox1_Change()
If TextBox1.TextLength = 4 Then
ActiveCell.Value = TextBox1.Value
TextBox1.Value = ""
ActiveCell.Offset(0, 1).Select
TextBox1.Activate
End If
End Sub

John
 
No you can't, any event macro is triggered when you leave the cell. Of
course that macro could strip
the cell of excessive digits when it is triggered. You can also take a look
at validation that would prevent
entering more than 4 digits, it is also triggered upon leaving the cell
 
Maybe you can use a tiny userform.

I created a little one with only a textbox on it.
This was the code behind the textbox.

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

Static keyCtr As Long
Select Case KeyAscii
Case 48 To 57 'Numbers 0-9
keyCtr = keyCtr + 1
ActiveCell.Value = ActiveCell.Value & Chr(KeyAscii)
If keyCtr = 4 Then
If ActiveCell.Column > 6 Then
Cells(ActiveCell.Row + 1, 1).Activate
Else
ActiveCell.Offset(0, 1).Activate
End If
'clean up data in new cell?
'ActiveCell.ClearContents
keyCtr = 0
KeyAscii = 0
Me.TextBox1.Value = ""
End If
End Select

End Sub

Next cell to me meant the cell to the right--until I finished with column
F--then back to column A of the next row.

and I had to have a macro to show the userform.
Option Explicit
Sub testme01()
UserForm1.Show
End Sub

Not sure if you want/need the .clearcontents line. It might be safer just
telling the user to empty the cells they need to change first.

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