From: Brad E. on 25 May 2010 15:24 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.
From: Gary''s Student on 25 May 2010 15:45 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.
From: Gord Dibben on 25 May 2010 15:54 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. <BradE(a)discussions.microsoft.com> 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.
From: Rick Rothstein on 25 May 2010 17:28 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." <BradE(a)discussions.microsoft.com> wrote in message news:36274928-1B79-44D2-BD84-0D5EB676F45E(a)microsoft.com... > 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.
From: GS on 26 May 2010 02:26 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
|
Pages: 1 Prev: AutoFilter macro Next: GetOpenFilename code execution interrupted more mystery |