Gord...why won't this code automaticly formart data already in this column?

Lonnie

"Gord Dibben" wrote:

Which column?

I will amend for column E which is equal to 5

Change this line.......... If Target.Column 1 Then Exit Sub

to................. If Target.Column < 5 Then Exit Sub

Or re-write as below.......which is the same thing.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const My_Range As String = "E:E"
On Error GoTo ErrHandler
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(My_Range)) Is Nothing Then
With Target
.Formula = UCase(Target.Formula)
.Value = (Left(Target.Value, 2) & Mid(Target.Value, 3, 1)) & _
"--" & Right(Target.Value, 2)
End With
End If
ErrHandler:
Application.EnableEvents = True
End Sub


Gord

On Wed, 31 Jan 2007 08:02:00 -0800, Lonnie
wrote:

Gord...you are right I do not want the entire worksheet formatted like this,
only one column......how do I apply to just one column?

Thanks again for all your help!

Lonnie

"Gord Dibben" wrote:

Lonnie

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column 1 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
With Target
.Formula = UCase(Target.Formula)
.Value = (Left(Target.Value, 2) & Mid(Target.Value, 3, 1)) & _
"--" & Right(Target.Value, 2)
End With
ErrHandler:
Application.EnableEvents = True
End Sub

This is event code. Right-click on the sheet tab and copy/paste the above into
that sheet module.

Type 12qwe in any cell in Columns A:E and will return 12Q--WE in Upper Case as
shown.

As written code works only on column A If you have a particular range in mind
rather than Column A post back with that range.

I can't imagine you would want the entire worksheet formatted this way.


Gord

On Tue, 30 Jan 2007 09:30:01 -0800, Lonnie
wrote:

Yes you got it...the numbers and/or letters may not always be the same but I
want the format to be a: number, number, letter, dash, dash,letter, letter....

Thanks for any help you may be able to provide.

Lonnie

"Gord Dibben" wrote:

Lonnie

If the letters will always be just D--TQ then follow Gary's Student's
suggestion.

If you want to create an input mask for any combination of letters then you
can't do it with cell formatting alone.

You would need VBA event code to do that.

Enter 74dqt and cell will display 74D--QT

Enter 74rst and cell will display 74R--ST

Is that what you want?


Gord Dibben MS Excel MVP

On Mon, 29 Jan 2007 15:23:01 -0800, Gary''s Student
wrote:

Just select all the cells and then perform the formating.
--
Gary's Student
gsnu200703


"Lonnie" wrote:

followed you up to "D-TQ"....Ii want to be able to format the entire
spreadsheet with this format (number, number, letter, dash, dash, letter,
letter).

Thanks,

Lonnie

"Gary''s Student" wrote:

Format Cells.. Number Custom
General"D-TQ"
--
Gary's Student
gsnu200703


"Lonnie" wrote:

I want to create my own cell format like this format: 74D--TQ (the format
would have number, number, letter, dash, dash, letter, letter).
Can anyone help me create this type of format?