cell format: adding custom formats

[Guest]

I am trying to create a custom format that accepts numerical and Letter
entries. I can create a custom format that allows numerical additions but
not letters. For example, I went into custom formats and created this:
"06405P1"0 Thus, if in the cell I type 6 I get 06405P16 This is
correct. If in the same cell I type N6 I get N6 I do not get
06405P1N6 which is what I would like to have. Any help is appreciated.

 

[Don Guillett]

right click sheet tab>view code>insert this. works in col A below row 2

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 3 And Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
Target.Value = "06405P1" & Target
Application.EnableEvents = True
End Sub

 

[Don Guillett]

this might be better.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 3 And Target.Column <> 1 Then Exit Sub
If Target = " " Or Target = "" Then Exit Sub
On Error GoTo fixit
Application.EnableEvents = False
Target.Value = "06405P1" & Target
fixit:
Application.EnableEvents = True
End Sub