| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
custom cell formating
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
custom cell formating
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
custom cell formating
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
custom cell formating
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
custom cell formating
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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
custom cell formating
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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
custom cell formating
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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
custom cell formating
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? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
custom cell formating
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? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
custom cell formating
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? |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
custom cell formating
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 On Wed, 31 Jan 2007 10:49:01 -0800, Lonnie wrote: 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| expanding custom formatting without removing existing cell formatting? | Excel Worksheet Functions | |||
| custom formating a cell | Excel Worksheet Functions | |||
| Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
| Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
| Cell to follow content AND/OR formating of another cell | Excel Discussion (Misc queries) | |||
Linear Mode
