VBA TypeOf

This article will demonstrate the use of the VBA TypeOf Operator.

The VBA TypeOf Operator is used in determining the type of an object. This can be useful in enabling or disabling controls on a VBA form or to control the flow of code depending on what type of object is being used.

Using TypeOf to Control Code

We can use TypeOf to ensure that the selection made is the type of specific object that we require – for example, we may want to select a range and then use an IF statement to see what is selected.  If a range is selected, then we we will get a message telling us that a range is selected, but if a range is not selected, we will get a different message.

Let us select some cells on our worksheet.

Now, if we run the macro below, we will be told that we selected a Range.

Sub TestSelection()
  Dim rng As Object
  If TypeOf Selection Is Range Then
     MsgBox "A range has been selected!"
  Else
     MsgBox "Something else is selected"
  End If
End Sub

However, if we do not select a range and select something else – perhaps a chart – and then run the macro, we will get a different result!

Using TypeOf on Form Controls

VBA enables us to create interactive forms that the user can fill in and return data to the code to be used in various ways.  We can use the TypeOf operator to determine the type of controls that are being used on a form.

In the example below, I have created a user form with a variety of controls on it – a couple of Text Boxes, a Combo Box, 2 option buttons, 2 check boxes and 3 command buttons.

Using the code below, I can determine what type of controls are on the form by looping through all the controls on the form.   I have used the TypeName function to return a message with the type of the control, and have used a VBA IF Statement using the TypeOf function  to check what type of control is selected, and then a further message box to return that type of control.

Sub WhatControlType()
  Dim ctl As Object
  For Each ctl In Me.Controls
    MsgBox (TypeName(ctl))
'Use the TypeOf function to determine the object's type.
    If TypeOf ctl Is msforms.TextBox Then
      MsgBox ("The control is a TextBox.")
    ElseIf TypeOf ctl Is msforms.ComboBox Then
      MsgBox ("The control is a ComboBox.")
    ElseIf TypeOf ctl Is msforms.Label Then
      MsgBox ("The control is a Label.")
    ElseIf TypeOf ctl Is msforms.CommandButton Then
      MsgBox ("The control is a Command Button.")
    ElseIf TypeOf ctl Is msforms.CheckBox Then
      MsgBox ("The control is a Check Box.")
    ElseIf TypeOf ctl Is msforms.OptionButton Then
      MsgBox ("The control is an Option/Radio Button.")
    Else
      MsgBox ("The object is some other type of control.")
    End If
Next ctl
End Sub

This type of code can be very useful if we wish to enable or disable controls.   In the code below, when the form is first opened, the option buttons and check boxes are disabled.

Private Sub UserForm_Initialize()
  Dim ctl As Object
  For Each ctl In Me.Controls
     If TypeOf ctl Is msforms.CheckBox Then
        ctl.Enabled = False
     ElseIf TypeOf ctl Is msforms.OptionButton Then
        ctl.Enabled = False
     Else
        ctl.Enabled = True
     End If
   Next ctl
End Sub

To enable the option buttons and checkboxes, I have written some further code behind the Enable Controls button.

Private Sub cmdEnable_Click()
  Dim ctl As Object
  For Each ctl In Me.Controls
    If TypeOf ctl Is msforms.CheckBox Then
      ctl.Enabled = Not ctl.Enabled
    ElseIf TypeOf ctl Is msforms.OptionButton Then
      ctl.Enabled = Not ctl.Enabled
    End If
  Next ctl
End Sub

Now when we click on the Enable Controls button, if the controls are disabled, they will become enabled and if they are enabled they will become disabled.  This is achieved using the Not Operator which enables us to toggle between disabled and enabled.