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.
|
1 2 3 4 5 6 7 8 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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.
|
1 2 3 4 5 6 7 8 9 10 |
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.
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
Learn More!
