Format Text as UppercaseLowercase With Excel VBA

Objective

We want to create a VBA macro that allows the user to change the text of selected cells to either Uppercase or Lowercase.

Step-by-Step VBA Code

1. Open Excel and Access the VBA Editor:
   • Open Excel.
   • Press Alt + F11 to open the VBA editor.
2. Create a New Module:
   • In the VBA editor, right-click on any of the existing items in the « Project Explorer » pane.
   • Select Insert > Module. This will create a new module where you can write your code.
3. Write the VBA Code:

Sub FormatTextUpperLower()
    ' Declare variables
    Dim selectedRange As Range
    Dim cell As Range
    Dim choice As Integer  
    ' Prompt the user to choose formatting option
    choice = MsgBox("Do you want to convert the text to Uppercase? " & _
                    "Click Yes for Uppercase, No for Lowercase.", _
                    vbYesNoCancel + vbQuestion, "Choose Text Format")
    ' Exit if user cancels
    If choice = vbCancel Then
        Exit Sub
    End If  
    ' Get the selected range of cells
    Set selectedRange = Selection
    ' Loop through each cell in the selected range
    For Each cell In selectedRange
        ' Check if the cell contains text
        If cell.HasFormula = False And Not IsEmpty(cell.Value) Then
            If choice = vbYes Then
                ' Convert text to Uppercase
                cell.Value = UCase(cell.Value)
            ElseIf choice = vbNo Then
                ' Convert text to Lowercase
                cell.Value = LCase(cell.Value)
            End If
        End If
    Next cell
End Sub

Detailed Explanation

Let’s go through the code in detail.

1. Variable Declaration:
   • selectedRange As Range: This will hold the range of cells that the user selects. This is important because we want to apply the formatting only to the cells the user selects.
   • cell As Range: This represents each individual cell in the selected range. We’ll loop through each cell to apply the format.
   • choice As Integer: This will store the result of the message box. Based on the user’s response, we will decide whether to convert the text to Uppercase or Lowercase.
2. User Prompt (Message Box):
   • The MsgBox function is used to show a dialog box to the user asking if they want to convert the text to Uppercase. The options in the message box are « Yes » and « No », with an additional « Cancel » option.
   • vbYesNoCancel is a constant that provides the user with three options: Yes, No, and Cancel. If the user clicks « Yes, » we will convert the text to Uppercase. If the user clicks « No, » we will convert the text to Lowercase. If they click « Cancel, » the script will stop executing.
3. Exit if Cancel is Chosen:
   • If the user clicks « Cancel » on the message box, choice = vbCancel is checked. If true, the Exit Sub command is executed, which stops the macro and does nothing further.
4. Get the Selected Range:
   • Set selectedRange = Selection: This line sets selectedRange to the range that the user has selected in the Excel worksheet. The Selection object refers to the currently selected cells.
5. Loop Through Each Cell:
   • We loop through each cell in the selected range with the For Each cell In selectedRange loop. This allows us to apply the formatting to each individual cell in the selection.
6. Check if the Cell Contains Text:
   • If cell.HasFormula = False And Not IsEmpty(cell.Value) Then: This condition ensures that we only format cells that do not contain formulas and are not empty.
     • cell.HasFormula = False: Checks if the cell has a formula. If the cell contains a formula, we skip it and don’t change it.
     • Not IsEmpty(cell.Value): Ensures that we only apply the formatting to cells that actually have a value (text, number, etc.). Empty cells are skipped.
7. Convert to Uppercase or Lowercase:
   • UCase(cell.Value) and LCase(cell.Value) are VBA functions that convert the text to Uppercase and Lowercase, respectively.
     • UCase: Converts all letters in a text string to uppercase.
     • LCase: Converts all letters in a text string to lowercase.
   • Depending on the user’s response from the message box (vbYes for Uppercase and vbNo for Lowercase), the text in the cell is converted.
8. Finish the Loop:
   • After the text is converted for each cell, the loop moves to the next cell in the selection.

How to Run the Code

1. Close the VBA editor and go back to Excel.
2. Select a range of cells that contain text that you want to format.
3. Press Alt + F8, select the FormatTextUpperLower macro, and click Run.
4. A message box will appear. Choose whether you want to convert the selected text to Uppercase or Lowercase by clicking Yes or No. If you click Cancel, the operation will stop.

Additional Notes

• Error Handling: This code does not contain error handling for invalid input or unexpected situations (e.g., non-text values like numbers or dates). If you want to improve this, you can add additional checks to ensure that the cell contains text before applying the format.
• Formatting Specificity: The current code only works for text values. It doesn’t handle dates or numbers in a specific way. If needed, you can modify it to work differently with non-text values.
• Performance Considerations: This code processes each cell individually in a loop. For a very large range, this might be a bit slow. Optimizations could be made for processing large ranges.