Add Change Case Buttons to the Microsoft Excel 365 Ribbon

If you work with both Microsoft Word and Microsoft Excel, you may have noticed that Microsoft Word has a “change case” button in the Font section of the ribbon which allows you to quickly capitalize or lowercase sections of your document. While Excel does not come with such a button by default, it is possible to add buttons to set the case of the highlighted cells.

Step 1: Add New Macros to Your Personal Macro Workbook

The personal macro workbook allows you to store macros that you would like to use in multiple workbooks. Once we’ve added macros to change case to the personal macro workbook, we can add buttons for them on the ribbon. Ensure the developer tab is enabled on the ribbon. Then, to make sure you have a personal macro workbook, go to Developer > Record Macro and record a macro. Stop recording, and open the Visual Basic editor from the same tab. In place of the recently recorded macro, add the following code:

Sub UpperCase()
'
' UpperCase Macro
'

'
    Dim cell As Range
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Check if the cell contains text
        If Not IsEmpty(cell.Value) And VarType(cell.Value) = vbString Then
            cell.Value = UCase(cell.Value) ' Capitalize the text
        End If
    Next cell
End Sub

Sub LowerCase()
'
' LowerCase Macro
'

'
    Dim cell As Range
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Check if the cell contains text
        If Not IsEmpty(cell.Value) And VarType(cell.Value) = vbString Then
            cell.Value = LCase(cell.Value) ' Lower case the text
        End If
    Next cell
End Sub

Sub ProperCase()
'
' ProperCase Macro
'

'
    Dim cell As Range
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Check if the cell contains text
        If Not IsEmpty(cell.Value) And VarType(cell.Value) = vbString Then
            cell.Value = StrConv(cell.Value, vbProperCase) ' Proper case the text
        End If
    Next cell
End Sub

Step 2: Add Change Case Buttons to the Ribbon

Right click the ribbon, and click “Customize the Ribbon…”. On the left, select Macros and move the macros into the desired area of the ribbon. Use the “Rename…” button to name the buttons and select an icon if desired.

That’s it! Because these macros are associated with your personal macro workbook, you can use the new buttons any time you are using Excel.

How to Use VBA To Run a Powershell Command and Get Return Value

If you need to interface with some sort of external command line program during your macro, you may want to do that using powershell as you normally would if you were running the program using the command line yourself. Once run, you may need to obtain a result from this command. I recently had to do this myself when working with the Google Translate API using VBA. This is easy to do in VBA. Checkout the below example, using a PowerShell command from VBA to get the user’s free disk space.

Continue reading “How to Use VBA To Run a Powershell Command and Get Return Value”