Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Formats
My Understanding: When using VALUES, one can format a cell to display a SSN
or ZIP+4 using Format Cells Custom. Of course, Excel has a couple built in, including these two, but you could make any format you want as long as the entry is a value. My Problem: I have part numbers which start with an Alpha character followed by 7 digits, then a dash (minus) and two more digits. Is there a way to format my cell entry to include the dash when the user just enters the 10-character part number? Specific Example: If my part number if P1234567-89, users sometimes enter P123456789. I would like to custom format the cell to automatically put a dash in if it is not included. Not all my parts start with P. -- TIA, Brad E. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Formats
Better still, the macro can auto-correct on the fly. Say data is being
entered into column A. Put the following event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set A = Range("A:A") If Intersect(t, A) Is Nothing Then Exit Sub v = t.Value If Len(v) = 11 Then Exit Sub If Len(v) = 10 Then Application.EnableEvents = False t.Value = Left(v, 8) & "-" & Right(v, 2) Application.EnableEvents = True End If End Sub So if the length of the entry is 11, it is left alone. If the length is 10, then the dash is inserted. Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu201003 "Brad E." wrote: My Understanding: When using VALUES, one can format a cell to display a SSN or ZIP+4 using Format Cells Custom. Of course, Excel has a couple built in, including these two, but you could make any format you want as long as the entry is a value. My Problem: I have part numbers which start with an Alpha character followed by 7 digits, then a dash (minus) and two more digits. Is there a way to format my cell entry to include the dash when the user just enters the 10-character part number? Specific Example: If my part number if P1234567-89, users sometimes enter P123456789. I would like to custom format the cell to automatically put a dash in if it is not included. Not all my parts start with P. -- TIA, Brad E. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Formats
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Cell As Range Const WS_RANGE As String = "B1:B10" 'adjust range to suit Application.EnableEvents = False On Error Resume Next If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then For Each Cell In Intersect(Target, Me.Range(WS_RANGE)).Cells With Cell .Value = Left(Cell, Len(Cell) - 2) & "-" & Right(Cell, 2) End With Next End If On Error GoTo 0 Application.EnableEvents = True End Sub No error-checking for entries less than or greater than 10 characters. Gord Dibben MS Excel MVP On Tue, 25 May 2010 12:24:11 -0700, Brad E. wrote: My Understanding: When using VALUES, one can format a cell to display a SSN or ZIP+4 using Format Cells Custom. Of course, Excel has a couple built in, including these two, but you could make any format you want as long as the entry is a value. My Problem: I have part numbers which start with an Alpha character followed by 7 digits, then a dash (minus) and two more digits. Is there a way to format my cell entry to include the dash when the user just enters the 10-character part number? Specific Example: If my part number if P1234567-89, users sometimes enter P123456789. I would like to custom format the cell to automatically put a dash in if it is not included. Not all my parts start with P. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Formats
As others have pointed out, you can use event programming to correct the
entry (formatting would only change the display of the entry, not the actual entry itself, so formatting would not be the appropriate way to do what you asked). Here is my Change event suggested solution... Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Columns("C")) Is Nothing Or Target.Value = "" _ Or Target.Value Like "[a-zA-Z]#######-##" Then Exit Sub If Target.Value Like "[a-zA-Z]#########" Then Application.EnableEvents = False Target.Value = Left(Target.Value, 8) & "-" & Right(Target.Value, 2) Application.EnableEvents = True Else MsgBox "That entry is incorrect", vbCritical, "Bad Entry" End If End Sub You can set the column to monitor in the first If statement (inside the Columns property call)... just change it to the column letter you want to apply this functionality to. Also note that the code will warn the user if the entry does not meet the correct format (letter followed by 9 digits). -- Rick (MVP - Excel) "Brad E." wrote in message ... My Understanding: When using VALUES, one can format a cell to display a SSN or ZIP+4 using Format Cells Custom. Of course, Excel has a couple built in, including these two, but you could make any format you want as long as the entry is a value. My Problem: I have part numbers which start with an Alpha character followed by 7 digits, then a dash (minus) and two more digits. Is there a way to format my cell entry to include the dash when the user just enters the 10-character part number? Specific Example: If my part number if P1234567-89, users sometimes enter P123456789. I would like to custom format the cell to automatically put a dash in if it is not included. Not all my parts start with P. -- TIA, Brad E. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Formats
Adding to the posted suggestions...
Rather than using Left(), Right() and concatenation: Target.Value = Format(Target.Value, "!@@@@@@@@-@@") Garry -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text & Number Formats | Excel Discussion (Misc queries) | |||
Date Formats in Text Box | Excel Discussion (Misc queries) | |||
set all column formats to Text | Excel Worksheet Functions | |||
Can you retain different text formats when merging text? | Excel Discussion (Misc queries) | |||
skipping text formats | Excel Programming |