Automatic Convert to Proper Case

A

Anne Troy

New member
Joined
Mar 25, 2011
Messages
23
Reaction score
0
Points
0
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). :eyebrows:
 
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,
 
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?

4-27-2011 3-26-55 PM.jpg
 
Very bizarre. I don't have it at home either! I'm running 2007 at work and 2010 at home.
 
I just found a thread online that says "add a userform and it appears" and it did. Will try that at work tomorrow. Thanks!
 
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...
 
You must log in or register to reply here.
Back
Top