Return to VBA Code Examples

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.

VBA Text TypeOf Select Range

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

VBA Text TypeOf Select Range_Msg

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!

VBA Text TypeOf Select Chart Msg

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.

VBA Text TypeOf Form Intro

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.

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.

VBA Text TypeOf_Form

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

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! vba save as


Learn More!