005 VBA Excel Form Control & ActiveX Control
Posted by Superadmin on June 23 2019 05:59:56

VBA Excel Form Control & ActiveX Control

 

 

Creating VBA Form/GUI controls in Excel

GUI is the acronym for Graphical User Interface. The GUI is the part of the program that the user interacts with. A GUI is made up of controls. These controls can be used in a Form. The following table shows some of the most commonly used GUI controls in VBA.

S/N Control Description
1 Command Used to execute code
2 Combo Box Used to present a drop down list to the users
3 CheckBox Used for true or false values. Multiple check boxes can have true value at the same time.
4 List Box Used to present a simple list to the users
5 Text Box Used to accept user input
6 Scroll Bar Used for to provide a scrollbar for navigation purposes.
7 Spin Button Used to increment or decrease numeric values by clicking on up or down arrows that appear in the spin button
8 Option Button Used for true or false values. Only one option button can have a true value at a time.
9 Label Used to display static text to the user
10 Image Used to display images
11 Toggle Button Button that has pressed and un-pressed states.

In the GUI control,

  1. Click on the developer tab
  2. Click on Insert Drop down button

You will get the following drop down panel

VBA Operators

If you hover the mouse over control, the name of the control will appear as shown below

VBA Operators

Adding GUI controls to a spreadsheet

We will now add a command button to our workbook, for that we have to

VBA Operators

Setting GUI control properties

We will now set the caption of the button to something more descriptive

VBA Operators

  1. Right click on the equal button that we have just created
  2. Select properties menu
  3. You will get the following properties window

VBA Operators

Close the window when you are done.

You will get the following results.

VBA Operators

How to use ActiveX control in VBA

In this section, we will see how to incorporate 'commandclick' button in VBA and execute a program using the button.

Step 1) In this step, click the option "insert button" from the Active X Control. Then select the command button option from it.

What is VBA?

Step 2) To insert "clickcommandbutton1" drag the mouse cursor to Excel sheet.

What is VBA?

Step 3) Then right click on the command button and select option "View Code".

What is VBA?

Step 4) Check you are on the right sheet. A code editor will open. Enter your code.

What is VBA?

Step 5) In next step, save code file and then exit the editor. To return to the Excel file click the Excel sheet icon What is VBA? on the extreme left.

What is VBA?

Step 6) In Excel sheet, you will see Design Mode tab is on. Make sure it is "OFF" or else your code will not work.

What is VBA? 

Step 7) When design mode is off, there will be no green highlight on it. Now you can Click on the command button.

What is VBA?

Step 8) Click on "CommandButton1". It will print "Guru99 VBA Tutorial" in the cell range "A1 to A10".

What is VBA?

Download the above Excel Code

Prerequisite

Configure Microsoft Excel

As we see in previous tutorial, make sure your ribbon will have "Developer" icon as shown here.

VBA Operators

Now, rename sheet1 to "VBA Operator" as shown in screen-shot below. Save the workbook in an Excel Macro-Enabled Workbook (*.xlsm) file extension. (see the image below).

VBA Operators

After that, click on Save button.