limit cell input
 

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?

limit cell input
 

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?

limit cell input
 

Jim - thanks very much. This works great!

"Jim Cone" wrote:


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?

limit cell input
 

You are welcome. The feedback is appreciated.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"DHSBob"
wrote in message
Jim - thanks very much. This works great!




"Jim Cone" wrote:
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?