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?

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?

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?

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?