Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto correct Name Format Entered (xls 97)

    I feel like I am abusing this board but is it possible if I have a cell where a name should be entered, i.e Johnatan Smith. If the user enters John Smith, J Smith, or Jo. Smith can I auto correct in excel to transform it to J. Smith. I want all of those cells to look the same. They should be First Initial,Period, Space, Last. (J. Smith). Thanks a million guys I owe you all. Once i learn something i will share also.

    Ed

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Auto correct Name Format Entered (xls 97)

    It will be easier to manage if you have First, Middle and Last in separate cells and then clean them up via ether formulas or VBA , and then finally concatenate the results into a fourth cell. You'll also need to consider rules to handle, "Jr., Sr." and "II", "III", etc.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Auto correct Name Format Entered (xls 97)

    You could use the Worksheet_Change event to alter the cell entry to what is suitable. Something like the following might work :-<pre>Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, ActiveSheet.Range("A:A")) _
    Is Nothing Then
    Dim strCell As String
    Dim strRet As String
    Dim intMid As Integer
    strCell = Target.Value
    If Not IsNumeric(strCell) And strCell <> "" Then
    Application.EnableEvents = False
    intMid = InStr(1, strCell, " ", 1)
    strRet = Left(strCell, 1) & ". " & _
    Right(strCell, Len(strCell) - intMid)
    Target.Value = strRet
    Application.EnableEvents = True
    End If
    End If
    End Sub</pre>

    Right click on the sheet tab of the workseet you want to work with, select View Code and place the above code in the main window. It is setup to monitor column A , but you can change that by altering Range("A:A") to whatever columns you want. Ideally I expect it should trap more erroneous entries, but try it and see if it works in your situation.

    Andrew C

  4. #4
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto correct Name Format Entered (xls 97)

    Hi Andrew the code works fine once i fixed it for the range i needed. Your directions were great. If a person had a middle name could i edit this could to display it as J. D. Smith. So its First Initial., Middle Initial., Last. Sorry for the bother, but its something I just realized migth be a problem. You have no idea how much youve helped, thanks.

  5. #5
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto correct Name Format Entered (xls 97)

    Never mind the middle initial stuff, not using that anymore.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Auto correct Name Format Entered (xls 97)

    Thats ok with me.

    Cheers

  7. #7
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto correct Name Format Entered (xls 97)

    For some reason i am getting a run time error 13 problem when i use the code. This is what i am doing. A name gets selected from a drop down list on sheet 2 in the format ( First, Last). i then need to have this appear as (F. Last) on sheet 1. I figured i could copy the info from the list in sheet2 into sheet 3 and run the code from sheet 3.i could then copy sheet 3 into sheet 1. nobody sees sheet 3 so thres no harm. i cant just run the code in sheet 1 because those cells have the formula

    ='Data Entry'!I3 & CHAR(10) & 'Data Entry'!J3 & CHAR(10) & 'Data Entry'!K3
    it appears VB wont run the code because the cell refernces take presedent over coding format, is that true? either way when the code gets entered into sheet 3 and i try to copy and paste the info from sheet 2 the line

    strCell = Target.Value

    gives me an error.

    What am i doing wrong????

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto correct Name Format Entered (xls 97)

    That's impossible to tell without seeing the rest of your code. Copy and paste the entire procedure/function.
    Legare Coleman

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Auto correct Name Format Entered (xls 97)

    The code posted was really only intended for cell by cell data entry. I suspect in your case you are using a formula and copying/dragging it down. The code as posted can only handle changes made to a single cell at a time, so if you edit more than one cell at a time you will get errors.

    If you are using formulae and not accepting direct data entry, then the formula could (hopefully) include the functionality of the code and you may not need to code.

    If you could post a sample of you workbook with just enough dummy data included I am sure somebody could provide you with a formula, or suggest an alternate approach.

    Andrew C

  10. #10
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto correct Name Format Entered (xls 97)

    Here you go, more details are on the INFO tab. thanks.
    Ed
    Attached Files Attached Files

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto correct Name Format Entered (xls 97)

    There are numerous problems with using the Change event to accomplish what you are trying to do. First, the cell contains a formula, and the change event is not going to trigger when the cell changes because the formula recalculated. Second, your code would replace the formula with a constant and any changes to the source cells after that would not show up on the clean up sheet. Third, the code in your vba routine will not properly handle the names that are combined in the cell.

    There are a couple of solutions to this problem. I have attached a modified version of your worksheet that I think does what you want with a formula in cell D1 on the clean up sheet without using the change event code. If this does not work, then you could put code in the worksheet change event for the data entry sheet. This code would look for changes in any of the columns where the names are entered. If any cell in these columns is changed, it would then loop through all of the name cells in that row and create a string that contained the concatinated names in the format that you want on the cleanup sheet and then put the result in the appropriate cell on that sheet.
    Attached Files Attached Files
    Legare Coleman

  12. #12
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto correct Name Format Entered (xls 97)

    Does the job perfecty but i get the #VALUE error in all cells that do not have dat on DATA ENTRY SHEET. I tried an IF ISERROR but i couldn't get itt o work. That was something i learned here. Am i trying the right thing? Is there a way to not display the # VALUE? So close i can smell it....
    ED

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto correct Name Format Entered (xls 97)

    Try the attached.
    Attached Files Attached Files
    Legare Coleman

  14. #14
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto correct Name Format Entered (xls 97)

    WORKS GREAT!!!! can't thank enough.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •