Change Word macro to Excel macro 2

[calvinb]

I have a Word macro that I got working with a lot of help from a few forum members for which I am very thankful.

Now, due to some changes at work, I have to switch to using Excel 2010 rather than Word. Could someone change the following Word code to work in Excel 201?

In the Word document the target text was copied and pasted and the macro worked on it while it was still selected. In the new Excel file, the names will be in an Excel cell so it should be easier to work with.

Function ProperCase(strOneLine As String, intChangeType As Integer) As String

'---------------------------------------------------------------
'- This function will convert a string to Proper Case          -
'- The initial letter of each word is capitalised.             -
'- It will also handle special names such as O', Mc and        -
'- hyphenated names                                            -
'- if intChangeType = 1, all text is converted to proper case. -
'- e.g. 'FRED' is converted to 'Fred'                          -
'- if intChangeType = 0, upper case text is not converted.     -
'- e.g. 'fred' becomes 'Fred', but 'FRED' remains unchanged.   -
'---------------------------------------------------------------

Dim I As Integer
Dim bChangeFlag As Boolean
Dim strResult As String

'----------------------------------------------------------
'- No characters in string - nothing to do                -
'----------------------------------------------------------
If Len(strOneLine) = 0 Then
    ProperCase = ""
    Exit Function
End If

'----------------------------------------------------------
'- Always set first letter to upper case                  -
'----------------------------------------------------------
strResult = UCase$(Left$(strOneLine, 1))

'----------------------------------------------------------
'- Now look at the rest of the string                     -
'----------------------------------------------------------
For I = 2 To Len(strOneLine)
    
'----------------------------------------------------------
'- If the previous letter triggered a capital, change     -
'- this letter to upper case                              -
'----------------------------------------------------------
    If bChangeFlag = True Then
        strResult = strResult & UCase$(Mid$(strOneLine, I, 1))
        bChangeFlag = False
'----------------------------------------------------------
'- In other cases change letter to lower case if required -
'----------------------------------------------------------
    Else
        If intChangeType = 1 Then
            strResult = strResult & LCase$(Mid$(strOneLine, I, 1))
        Else
            strResult = strResult & Mid$(strOneLine, I, 1)
        End If
    End If
    
'----------------------------------------------------------
'- Set change flag if a space, apostrophe or hyphen found -
'----------------------------------------------------------
    Select Case Mid$(strOneLine, I, 1)
    Case " ", "'", "-", "’"     'the last quote is done by holding ALT and typing 0146. True curly quotes!!
    
        bChangeFlag = True
    Case Else
        bChangeFlag = False
    End Select
Next I

'----------------------------------------------------------
'- Special handling for Mc at start of a name             -
'----------------------------------------------------------
    If Left$(strResult, 2) = "Mc" Then
        Mid$(strResult, 3, 1) = UCase$(Mid$(strResult, 3, 1))
    End If
    
    I = InStr(strResult, " Mc")
    If I > 0 Then
        Mid$(strResult, I + 3, 1) = UCase$(Mid$(strResult, I + 3, 1))
    End If
   
'----------------------------------------------------------
'- Special handling for Mac at start of a name             -
'----------------------------------------------------------
   If Left$(strResult, 3) = "Mac" Then
    If Len(Split(Trim(strResult), " ")(0)) > 5 Then
        Mid$(strResult, 4, 1) = UCase$(Mid$(strResult, 4, 1))
    End If
End If
I = InStr(strResult, " Mac")
If I > 0 Then
    If Len(strResult) > I + 5 Then
        Mid$(strResult, I + 4, 1) = UCase$(Mid$(strResult, I + 4, 1))
    End If
End If

   
ProperCase = strResult

End Function

Thanks for your help!!

 

[PHV]

Your code compiles perfectly in Excel, so what is your problem ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[calvinb]

Oopa!!!

I didn't try it in Excel. I thought it would be different. Sorry for the inconvenience but I learned to try it first without making assumptions!

Thanks

 

[calvinb]

I'm back!! I do still have a problem - not knowing enough about Excel... lol

On the Excel sheet I have, I will be pasting names into a cell that then have to be processed by this macro to be formatted correctly. Where do I put the macro and how to I get it to do it's thing on the cell when the name is pasted?

I would prefer to not have to click a button or have to do anything after the pasting. Is there something like a "change event" where code can be inserted so it will work when anything is pasted into the cell?

Thanks for your help!

 

[PHV]

I'd use the Worksheet_Change event procedure.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[calvinb]

Well, I've just wasted a few hours trying to get this to work in the Worksheet_Change event!

The cell that will contain the name is M4 on a worksheet named "Form". I put the function in the Wooksheet_Change event and set Target to that address:

Target.Address = $M$4

When I changed the name and hit Enter, nothing seems to happen other than moving to the next cell!

What am I doing wrong

 

[PHV]

Don't set the address of the target but test it!

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

I'd try this:

If Not Intersect(Target, Range("M4")) Is Nothing Then
  Application.EnableEvents = False
  Range("M4") = ProperCase(Range("M4"), 1)
  Application.EnableEvents = True
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[calvinb]

Thanks for your reply, PHV. That works fine if I type in the name in cell M4 and then tab out. It formats properly... so thank you for that.

But.... what I am doing is pasting a table in Excel, consisting of a list of names and other info, and then cell M4 is linked to the names using this formula:

INDIRECT("Data!C" &RowIndex+3)

So when the name appears in cell M4, I want the ProperCase function to correct the capitalization but it does nothing!

Any ideas?

Thanks!

 

[SkipVought]

in an empty column....
[tt]
=ProperCase(INDIRECT("Data!C" &RowIndex+3), 1)
[/tt]
assuming that INDIRECT("Data!C" &RowIndex+3) is a reference to a NAME that you want to capitalize.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[calvinb]

Thanks to PHV and Skip. It is working fine.

Skip, your solution to the ProperCase thing is excellent... so simple but so effective!! Would you tell me what the " ,1 " at the very end does?

 

[SkipVought]

From your posted function, and should be read and understood in order to use the function correctly...

'- if intChangeType = 1, all text is converted to proper case. -
'- e.g. 'FRED' is converted to 'Fred'                          -
'- if intChangeType = 0, upper case text is not converted.     -
'- e.g. 'fred' becomes 'Fred', but 'FRED' remains unchanged.   -

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}