custom cell formating

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
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
 
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
 
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
 
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
 
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
 
Lonnie

It is Change event code.

A cell has to be changed before the code will trigger.

With existing cells you must force a change.

For each cell hit F2 then ENTER.


Gord
 
Back
Top