limit cell input

D

DHSBob

My problem is that I need to restrict cell input to a specific format. The
cell will have a total of 10 characters. The first character will be an
alpha character such as an A or a T. The last nine characters will be
numeric. The cell can contain no spaces or other characters not previously
listed. The final result would look something like A123456789.

I tried using data validation and cell formating bot neither has done enough.

I'm using Office 2003. Any suggestions?
 
J

Jim Cone

Following code goes in the module for the particular sheet.
Right-click the sheet tab and choose "View Code"...
Change the cell address in the line noted with <<<<.
'--
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo AllOver
If Target.Address = "$B$5" Then '<<<<
Application.EnableEvents = False
If Not Target.Value Like "[a-z]#########" Then
MsgBox "alpha character followed by 9 digits only ", _
vbExclamation, "Pay Attention"
Application.Undo
End If
End If
AllOver:
Application.EnableEvents = True
End Sub
'--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"DHSBob"
wrote in message
My problem is that I need to restrict cell input to a specific format. The
cell will have a total of 10 characters. The first character will be an
alpha character such as an A or a T. The last nine characters will be
numeric. The cell can contain no spaces or other characters not previously
listed. The final result would look something like A123456789.

I tried using data validation and cell formating bot neither has done enough.
I'm using Office 2003. Any suggestions?
 

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