Results 1 to 8 of 8

Thread: Automatic Convert to Proper Case

  1. #1

    Automatic Convert to Proper Case



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a workbook to do some cool little tricks for me, including building text strings I need to paste into a status update database.

    That database stores all data in caps, but I send emails out and such where I grab that data and don't want it in all caps.

    I'd like to paste it into a cell on a worksheet, Cell G21.
    I'd like it to then automatically copy the contents of Cell F21 and copy it to the clipboard.

    I have another instance where I paste a phone number as 0000000000 and want to auto-convert to (000) 000-0000, which I am doing with another formula. I'd like to keep the formulas in the cells in case I want to use this for other formulas I create later.

    1. Is it possible?
    2. Can it be written to specific cell references so I can do it for a couple of other cells as well?
    3. Can a worksheet change event apply to a specific cell?
    4. I anyone feeling generous today? Generous enough to write that code for me? (No laughing).

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,165
    Articles
    57
    Blog Entries
    14
    Be warned, no data validation on this, but if your data is valid it will work.

    Drop it in the worksheet module, and don't forget to set a reference to the Microsoft Forms 2.0 Object Library.

    I set the second range to G22.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    'Requires reference to Microsoft Forms 2.0 Object Library
        Dim DataObj As New MSForms.DataObject
        Dim sDataToCopy As String
        Application.EnableEvents = False
        On Error GoTo ExitPoint
        Select Case Target.Address
        Case Is = "$G$21"
            sDataToCopy = Application.WorksheetFunction.Proper(Target.Value)
        Case Is = "$G$22"
            sDataToCopy = Format(Target.Value, "(000) 000-0000")
        End Select
        DataObj.SetText sDataToCopy
        DataObj.PutInClipboard
    ExitPoint:
        Application.EnableEvents = True
    End Sub
    HTH,
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    I don't appear to have the Microsoft Forms 2.0 ??

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,165
    Articles
    57
    Blog Entries
    14
    Really? Under Tools-->References?

    I've always found that it's near the top of the list, but you might have to scroll down a bit?

    Name:  4-27-2011 3-26-55 PM.jpg
Views: 185
Size:  20.1 KB
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Very bizarre. I don't have it at home either! I'm running 2007 at work and 2010 at home.

  6. #6
    I just found a thread online that says "add a userform and it appears" and it did. Will try that at work tomorrow. Thanks!

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,165
    Articles
    57
    Blog Entries
    14
    Okay, that's bizarre... Glad to hear it showed up though... but very curious as to why it didn't initially.

    One of the first things when I install Excel is activate one of my own add-ins which has a userform. I wonder if that's why it's never been an issue for me? Still strange...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,165
    Articles
    57
    Blog Entries
    14
    Okay, Anne, I'm curious... did that make the Forms library manifest at work too?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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