This article is part of my series: VBA & Macros in Excel – A Step by Step Complete Guide and Make UserForms in VBA Excel with Total 6 Tutorials.
In this tutorial, I am going to create a userform that will change a selected cell’s text case. Three type of cases I have used here: Upper Case, Lower Case, and Proper Case. You know what Upper and Lower cases are. The Proper case is changing the first letter to Capital of every word in the text.
Table of Contents
Creating the UserForm
This UserForm will take one information from the user: the type (Upper, Lower or Proper) the user wants to apply to the text. OptionButton controls are appropriate for this type of selection. Open a new workbook and use the following steps to create the Userform:
- Choose Developer ➪ Code ➪ Visual Basic (or press Alt+F11) to open the VB Editor window.
- Double-click on the workbook’s name in the Project window to activate it.
- In the VB Editor, choose Insert ➪ UserForm. An empty new form named UserForm1 is created by the VB Editor and the Toolbox is displayed.
- I’m going to change the name of the Userform and add a caption. You can do these using the properties window. Press F4 to open the Properties window. Properties window has so many options you can manipulate. For now, just change the following:
Property Change to Name ChangeCaseForm Caption Change Case of the Text - Add a CommandButton control from the Toolbox to the UserForm and then change the following properties for the CommandButton:
Property Change to Name OKButton Caption OK Default TRUE - Add another CommandButton control in the UserForm and then change the following properties:
Property Change to Name CancelButton Caption Cancel Default TRUE - Now, add an OptionButton control and then change the following properties. By default, this option will be active, so its Value property should be set to True.
Property Change to Name OptionUpper Caption Upper Case Default TRUE - Add a second OptionButton control and then change the following properties:
Property Change to Name OptionLower Caption Lower Case - Add a third OptionButton control and then change the following properties:
Property Change to Name OptionProper Caption Proper Case - Adjust the size and position of the controls and the form until your UserForm looks like the UserForm shown in the figure below. The controls must not overlap.

The UserForm looks like this after adding some controls and properties.
Formatting several controls in the UserForm
Sometimes it is necessary to make several controls look alike. For example, in the created UserForm, the two CommandButtons should be the same in size and alignment. Same should be for three OptionButtons. The VB Editor provides several useful commands to help you size and align the controls. For example, we’re going to give three OptionButtons same size and alignment. Pressing CTRL select three OptionButtons and right click on the selection. You can also format choosing a command from the Format menu. See below.

You can format several controls using commands under Format menu to give them the same look.
Testing the UserForm
Well, our UserForm has all the necessary controls. Now, it’s time to display the UserForm. While you’re creating the UserForm, press F5 to display the UserForm and see whether it looks like what you want. Click the Close button (X) in the title bar to close the UserForm.
The following process explains how you can write a VBA Sub procedure to display the UserForm when Excel is active:
- Insert a VBA module by choosing Insert ➪ Module.
- In the empty module, enter the following code:
Sub ShowCaseChangeBox() ChangeCaseForm.Show End Sub
- Open your workbook pressing Alt+F11.
- Choose Developer ➪ Code ➪ Macros or press Alt+F8. The Macros dialog box appears.
- Select ShowCaseChangeBox from the list of macros, and then click Run. The UserForm appears. See the following figure.

Displaying the Userform that we have created so far.
- The OptionButton controls work, but clicking the OK or Cancel buttons do not show any effect. These two buttons need to have event handler procedures.
- Click the Close button (X) in the title bar to dismiss the UserForm.
Creating event handler procedures
An event handler is a private type Sub procedure that is executed when we perform the relevant event with the object.
Now, we shall create two event handler procedures: one will handle the Click event for the CancelButton, and the other will handle the Click event for the OKButton.
Read More: Why We Use Form Controls on a Worksheet?
Event handlers for the OptionButton controls are not necessary. They will be handled internally in the OKButton Sub procedure.
To create the Sub procedure to handle the Click event for the CancelButton, use the following steps:
- Activate the ChangeCaseForm by double-clicking its name in the Project window.
- Double-click the CancelButton control. The VB Editor activates the code module for the UserForm and inserts an empty procedure.
- Insert the following statement before the End Sub statement: Unload ChangeCaseForm
The complete event handler procedure is:
Private Sub CancelButton_Click() Unload ChangeCaseForm End Sub
This procedure will be executed when someone will click the Cancel button (CancelButton). The dialog box will vanish when this code executes.
The next step is to write code to handle the Click event for the OKButton control.
Follow these steps:
- You can select OKButton from the drop-down list at the top of the module. Or You can reactivate the UserForm and double-click the OKButton control. The VB Editor automatically starts a new procedure called OKButton_Click.
- Enter the following code. The complete event handler procedure is:
Private Sub OKButton_Click() Dim WorkRange As Range Dim cell As Range ChangeCaseForm.Hide ' Exit if a range is not selected If TypeName(Selection) <> "Range" Then Exit Sub Application.ScreenUpdating = False ' Avoid processing cells out of the used area Set WorkRange = Intersect(Selection, ActiveSheet.UsedRange) ' Upper case If OptionUpper Then For Each cell In WorkRange If Not cell.HasFormula Then cell.Value = StrConv(cell.Value, vbUpperCase) End If Next cell End If ' Lower case If OptionLower Then For Each cell In WorkRange If Not cell.HasFormula Then cell.Value = StrConv(cell.Value, vbLowerCase) End If Next cell End If ' Proper case If OptionProper Then For Each cell In WorkRange If Not cell.HasFormula Then cell.Value = StrConv(cell.Value, vbProperCase) End If Next cell End If End Sub
The macro demands that the user will select a cell or a range of cells. If a range is not selected, the procedure ends. The procedure then uses three separate blocks. Only one block is executed, dependent on which OptionButton is selected. The selected OptionButton has a value of True. Finally, when user clicks OK, the UserForm is unloaded.
Testing the UserForm
To test the UserForm, follow these steps:
- Activate Excel.
- Enter some text into a range of cells.
- Select the range with the text.
- Choose Developer ➪ Code ➪ Macros or press Alt+F8. The Macros dialog box appears.
- Select ShowCaseChangeBox macro from the list, and then click Run. The UserForm appears.
- Make your choice (Upper Case, Lower Case, or Proper Case), and click OK.
You must try this macro with more selections, including not connected cells. If you click Cancel, the UserForm is dismissed and no changes happen.
Read More: How to add a macro to your Quick Access toolbar in Excel
However, I want to make an easy way as though I can execute the macro in one click. A good way to execute this macro would be to set a button form on the worksheet. You can use the following steps:
- Choose Developer ➪ Controls ➪ Insert and click the Button control in the Form Controls group.
- Click and drag in the worksheet to create the button. The Assign Macro dialog box appears.
- Select the ShowCaseChangeBox macro, and then click OK.
- The button is still selected, so you can change the text to make it more descriptive. You can also right-click the button at any time to change the text.
Now you can execute the macro to show the UserForm just clicking the button. You can also change other formats like color, the size of text and etc. See the figure below:

ShowCaseChangeBox macro has been assigned to a button on the worksheet.
Making the macro available on your Quick Access toolbar
You may want that your this macro will be available when you are working with other workbooks. You have to add a button to the Quick Access Toolbar to achieve this target. Follow these steps:
- Make sure that the workbook containing the macro is open.
- Right-click anywhere on the Ribbon and choose Customize Quick Access Toolbar from the shortcut menu. The Excel Options dialog box appears. The Quick Access Toolbar section is selected.
- Choose Macros from the Choose Commands From the drop-down menu on the left. Well, our macro is listed there.
- Select the macro’s name, and click Add to add the item to the list on the right.
- You can also change the icon. To change the icon, click Modify and choose a new image. You can also change the Display Name.
- Click OK to close the Excel Options dialog box. The icon will appear on your Quick Access toolbar.

Adding the Macro in the Quick Access Toolbar.
The macro is added to the Quick Access Toolbar.

The Macro is added to the Quick Access Toolbar.
Download Working File
Download the working file from the link below:
Happy Excelling ☕