Convert text to uppercase or lowercase in Excel VBA

Objective:

We will create a VBA macro that will convert text to either uppercase or lowercase based on the user’s selection.

Explanation of the code:

1. Define the conversion function: We will use Excel’s built-in functions UCase to convert to uppercase and LCase to convert to lowercase.
2. Select the cell to process: The code will work on the selected cell(s).
3. Prompt the user to decide whether to convert to uppercase or lowercase.
4. Apply the conversion to the selected cells.

VBA Code:

Sub ConvertText()
    ' Declare a variable to store the user's choice
    Dim choice As String
    ' Ask the user whether they want to convert to uppercase or lowercase
    choice = InputBox("Enter 'M' to convert to uppercase or 'm' to convert to lowercase.", "Choose Conversion")
    ' Check if the user has selected a cell
    If TypeName(Selection) = "Range" Then
        ' Check if the selected cell is not empty
        If Not IsEmpty(Selection.Value) Then
            ' If the user chose 'M', convert to uppercase
            If choice = "M" Then
                Selection.Value = UCase(Selection.Value)
            ' If the user chose 'm', convert to lowercase
            ElseIf choice = "m" Then
                Selection.Value = LCase(Selection.Value)
            ' If the user enters an invalid choice, display an error message
            Else
                MsgBox "Invalid option. Please enter 'M' for uppercase or 'm' for lowercase.", vbExclamation
            End If
        Else
            MsgBox "The selected cell is empty.", vbExclamation
        End If
    Else
        MsgBox "Please select a cell containing text.", vbExclamation
    End If
End Sub

Explanation of the Code:

Asking for the conversion option:

choice = InputBox("Enter 'M' to convert to uppercase or 'm' to convert to lowercase.", "Choose Conversion")

• • This line displays an input box where the user can enter either « M » for uppercase or « m » for lowercase.

Check if the selection is valid:

If TypeName(Selection) = "Range" Then

• • This checks if the user has selected a valid range of cells. If no cell is selected, an error message will be shown.

Check if the cell is not empty:

If Not IsEmpty(Selection.Value) Then

• • This condition ensures that the selected cell is not empty before attempting to convert the text.

Convert to uppercase:

If choice = "M" Then
    Selection.Value = UCase(Selection.Value)

• • If the user chose « M », the UCase function is used to convert the text to uppercase.

Convert to lowercase:

ElseIf choice = "m" Then
    Selection.Value = LCase(Selection.Value)

• • If the user chose « m », the LCase function is used to convert the text to lowercase.

Error messages:

MsgBox "Invalid option. Please enter 'M' for uppercase or 'm' for lowercase.", vbExclamation

1. • If the user enters anything other than « M » or « m », an error message is displayed.

How to Use:

1. Insert the code into the VBA editor:
   • Press Alt + F11 to open the VBA editor.
   • In the menu, click Insert > Module.
   • Paste the code into the module window.
2. Run the macro:
   • Return to Excel and press Alt + F8, then select ConvertText and click « Run ».
   • A prompt will appear asking whether you want to convert to uppercase or lowercase.

Possible Improvements:

• Add a check to convert only text cells (ignoring empty or numeric cells).
• Extend the functionality to support other types of transformations, like title case (capitalizing each word).

This code simplifies the process of converting text in Excel, providing users with an easy way to choose between uppercase and lowercase conversions.