Is there a keyboard shortcut to change ALL CAPS to All Caps?

[JenniferMurphy]

One website that I follow has the annoying preference for ALL CAPS. I like to copy some of the lists to either a Word document or an Excel worksheet. In Word, the keyboard shortcut Shift+F3 will cycle the text between all upper case, all lower case, or one or two versions of first letter capitalization. (a) If the text ends with a period, then it assumes that it is a sentence and it will capitalize the just first letter of the first word. (b) Otherwise, it will capitalize the first letter of every word.

Here is an example:

Shift+F3 doesn't seem to work for me in Excel. Is there a way to activate it?

It would be even better if there were a fourth step for titles, where it would capitalize just the words that are normally capitalized on titles. Like this:

Is there something like this in Excel? Shift+F3 lets me enter a formula. If so, how do I use it. If not, can I add it?

Thanks

 

[bobsan42]

For anyone who wants to fool around, a bit slow if you don't have a Word App opened already, but interesting nevertheless:

Function changeCaseWordApp(ByVal txt As String, xCase As Long) As String
    'Available options for xCase:
        'wdLowerCase = 0
        'wdTitleWord = 2
        'wdUpperCase = 1
        'wdTitleSentence = 4
        'wdToggleCase = 5
        '
        'wdHalfWidth = 6
        'wdFullWidth = 7
        'wdKatakana = 8
        'wdHiragana = 9
        '
        'wdNextCase = -1

    Dim wdApp As Object
    Dim wdRange As Object

    Set wdApp = CreateObject("Word.Application") ' CreateWord 'alternative

    With wdApp
        .Visible = False 'True
        .documents.Add
        Set wdRange = .Selection.Range
        With wdRange
            .text = txt
            .Case = xCase
            changeCaseWordApp = .text
        End With

        .Quit False
    End With
    Set wdRange = Nothing
    Set wdApp = Nothing
    
End Function

'an alternative object creator I found somewhere on the web 
Public Function CreateWord (Optional bVisible As Boolean = True) As Object

    Dim oTempWD As Object

    On Error Resume Next
    Set oTempWD = getObject(, "Word.Application")

    If Err.Number <> 0 Then
        Err.Clear
        On Error GoTo ERROR_HANDLER
        Set oTempWD = CreateObject("Word.Application")
    End If

    oTempWD.Visible = bVisible
    Set CreateWord = oTempWD

    On Error GoTo 0
    Exit Function

ERROR_HANDLER:
    Select Case Err.Number

        Case Else
            MsgBox "Error " & Err.Number & vbCr & _
                " (" & Err.Description & ") in procedure CreateWord."
            Err.Clear
    End Select

End Function

 

[Perpa]

bobsan42,
I am curious why your line for 'Proper' format works in the code below, however a similar code for 'Upper' and 'Lower' causes 'error 438, Object doesn't support this property or method'.
Perhaps you might explain.
Thank you in advance.
Perpa

Private Function reCase(ByRef x As String, ByVal MyChoice As String) As String
    
If MyChoice = "allcaps" Then
reCase = UCase$(x)

'reCase = Application.WorksheetFunction.Upper(x)   'Causes Error 438

ElseIf MyChoice = "proper" Then 'allows upper or lower case selection
reCase = Application.WorksheetFunction.Proper(x)
ElseIf MyChoice = "lower" Then 'allows upper or lower case selection
reCase = LCase$(x)

'reCase = Application.WorksheetFunction.Lower(x)   'Causes Error 438

    End If
End Function

 

[bobsan42]

I cannot confirm immediately, but I guess that the worksheetfunction class only exposes functions which do not have an equivalent in VBA. And it does not expose all functions available to excel formulas.

 

[bobsan42]

To explore (most of) the availlable methods and properties of a class, put the cursor on its name in code and press Shift+F2 (or right click on the name and select Definition).
Same goes for variables in the code.

 

[MARK858]

however a similar code for 'Upper' and 'Lower' causes 'error 438,

Because VBA has UCase and LCase and so doesn't need a Worksheetfunction that does the same job.

A list of the Worksheetfunction methods available in VBA that Microsoft list is in the link below

WorksheetFunction object (Excel)

Office VBA reference topic

learn.microsoft.com