Okay, Peter's posting includes some error checking, so I decided to
incorporate error checking into my offering as well. Remove the code I gave
you earlier and copy/paste this into its place instead...
' ******* Start of Code *******
Dim LastCellVisited As Range
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TargetStrippedOfDashes As String
If Target.Column < 4 Then Exit Sub
On Error GoTo Whoops
Application.EnableEvents = False
TargetStrippedOfDashes = UCase(Replace(Target.Value, "-", ""))
If TargetStrippedOfDashes Like "###########[A-D]" Then
Target.Value = Format(TargetStrippedOfDashes, "&&-&&&&&&&&&-&")
ElseIf Len(Target.Value) 0 Then
LastCellVisited.Select
MsgBox "You last attempted entry was not properly formed"
End If
Whoops:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set LastCellVisited = Target
End Sub
' ******* End of Code *******
If you make an invalid entry, the code will return you to the cell with the
invalid entry and pop up a MessageBox advising you of the situation. Also,
you can now type in a value with one, two or no dashes (doesn't matter if
any entered dashes are placed correctly or not as I remove them and insert
properly located dashes... all that matters is that your entry has 11 digits
followed one of the letters A, B, C, D, in either upper or lower case, after
the dashes are removed).
Rick
"Rick Rothstein (MVP -
VB)" wrote in
message ...
Ah, now I see what you want. Okay, I don't think you can do this with a
cell format, but you can do it with a worksheet event procedure.
Right-click the tab for the sheet where you want this functionality to be
on and copy/paste this code into the code window that appears....
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 4 Then Exit Sub
On Error GoTo Whoops
Application.EnableEvents = False
Target.Value = Format(UCase(Target.Value), "&&-&&&&&&&&&-&")
Whoops:
Application.EnableEvents = True
End Sub
Note that I set this up for Column D (that is what the '< 4' test is
doing) but you can change this as needed. Now, go back to your sheet and
type in one of your numbers into a cell in Column D (don't type in any
dashes, the code will insert them for your)... hit Enter, Tab or click
into a different cell and watch what happens to your entry.
Rick
"Rick" wrote in message
...
Rick:
I will be typing the entire number and letter in the cell, I have the
first
part of the format which is 00-000000000-? the question mark is what is
missing. what character do I use to have a single alpha appear in that
position.
"Rick Rothstein (MVP - VB)" wrote:
Then I guess I am not following what you want or need. What are you
physically going to type into the cell you asked to be able to format?
Where
does the A, B, C, D you asked about come from? You are going to have to
give
us a little more detail about your layout, what is typed where, what you
want to look like what where, etc.
Rick
"Rick" wrote in message
...
The number part as well as the alpha part can be anynumber and any
alpha.
It
is a trace code for medical billing, but the format will always be the
same.
"Rick Rothstein (MVP - VB)" wrote:
Is the "number" part always going to be 07-108029827? In other words,
what
do you want to TYPE into the cell and what do you want appear? If the
number
part is not the same all the time, where is it coming from?
Rick
"Rick" wrote in message
...
Is there anyone out there that can tell me how to create a format
that
does
this:
07-108029827-A it's the A that needs to be a substitue alpa
character,
because the A may have to be a B, or C, or D.
Thanks