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.
It is impossible to use Excel for long without being exposed to dialog boxes. Excel uses dialog boxes to take information from the users, to make understand the commands given by the users, and to display messages to the users. Using VBA macros, we can create our own dialog boxes that works like the built one dialog boxes in Excel. Our created dialog boxes are called UserForms.
Why Create our own UserForm?
For example, you have an Excel worksheet. You have some sales data recorded in this worksheet. You have developed a macro. This macro uses the above sales data and calculates something using these data as per your requirement and then shows the results in another portion of your Excel sheet. So, this macro is developed to do the same thing every time.
You can develop the above macro in such a way that it will work with different data in different circumstances. In such cases, the macro may use custom dialog boxes (UserForm).
The following macro changes the text of a selected cell range to uppercase. But if the cell range has formula, macro does not change anything. The procedure uses VBA’s built-in StrConv function.
Sub ChangeCase()
For Each cell In Selection
If Not cell.HasFormula Then
cell.Value = StrConv (cell.Value, vbUpperCase)
End If
Next cell
End Sub
This macro is useful, but you can improve this macro. For example, the macro would be more useful if it could also change the cells to lowercase or proper case (only the first letter of each word is uppercase).
Like the following figure, you can make a dialog box to ask the user to know what type of case change she/he wants. You can create this type of custom dialog boxes using UserForm in the Visual Basic (VB) Editor. The created UserForm is displayed by a VBA macro. Using this link you can create a userform that will change cases to Upper, Lower or Proper.
This dialog box takes information from the user to decide what type of case changes Excel will perform.
There is another solution. You can create three macros. Three macros will handle three types of case changes. But this is a toilsome and inefficient way.
Happy Excelling ☕
