22.4 Creating a Form in Excel

In the following video, you’ll see an Excel worksheet with an interactive form that can be used to help you understand how to calculate the perimeter and area for a variety of shapes. It is a form that was custom-built by utilizing some of Excel’s built-in functionality, such as Controls found within the Developer tab, as well as some VBA.

Let’s discuss the steps to create a simple form on an Excel worksheet. UI controls can be found on the Developer menu in the Controls group.

You can see that you have the choice of adding either ActiveX controls or the Form Controls that are built into Excel. Typically, you will just use the controls in the first group. You can see that you can insert buttons, combo boxes, checkboxes, spinners, text, and so on. In our example, a few radio buttons were added to represent the different shapes. We’ve also implemented some images that were named.

Behind the scenes, some pretty simple VBA code was written. This code, called OptionButton_Click, gets executed anytime a radio button is clicked as it is the assigned macro to each of the radio buttons.

    Sub OptionButton_Click()
    Application.ScreenUpdating = False
    Rows("14:37").EntireRow.Hidden = True
    
    'hide images
    ActiveSheet.Shapes("imgSquare").Visible = False
    ActiveSheet.Shapes("imgRectangle").Visible = False
    ActiveSheet.Shapes("imgTrapezoid").Visible = False
    ActiveSheet.Shapes("imgTriangle").Visible = False
    ActiveSheet.Shapes("imgCircle").Visible = False
    
    Select Case Application.Caller
        Case "objSquare"
            Range("Square").EntireRow.Hidden = False
            Range("Square").Range("B2").Select
            ActiveSheet.Shapes("imgSquare").Visible = True
        Case "objRectangle"
            Range("Rectangle").EntireRow.Hidden = False
            Range("Rectangle").Range("B2").Select
            ActiveSheet.Shapes("imgRectangle").Visible = True
        Case "objCircle"
            Range("Circle").EntireRow.Hidden = False
            Range("Circle").Range("B2").Select
            ActiveSheet.Shapes("imgCircle").Visible = True
        Case "objTrapezoid"
            Range("Trapezoid").EntireRow.Hidden = False
            Range("Trapezoid").Range("B2").Select
            ActiveSheet.Shapes("imgTrapezoid").Visible = True
        Case "objTriangle"
            Range("Triangle").EntireRow.Hidden = False
            Range("Triangle").Range("B2").Select
            ActiveSheet.Shapes("imgTriangle").Visible = True
    End Select
End Sub

Notice how, initially, rows 14 to 37 are hidden, and all the images on the sheet are also hidden. Then, depending on which radio button was clicked, the code will hide certain rows and images and then write some simple VBA to hide certain rows, depending on what radio button was selected.

In the video, you will get a further look into the VBA written to make the worksheet interactive when a radio button is selected. It is important to note, however, that interactivity is made very simple by the use of named ranges. By naming each range of cells, it is very easy to identify which rows and images should be shown. Take a few minutes to explore the worksheet and make sure you understand how these components come together to create this interactive experience.