VBA Controls: VBA Form Control & ActiveX Controls in Excel - Shikshaglobe

Content Creator: Vijay Kumar

Making VBA Form/GUI controls in Excel

GUI is the abbreviation for Graphical User Interface. The GUI is the piece of the program that the client associates with. A GUI is comprised of controls. These Excel VBA controls can be utilized in a Form. The accompanying table shows probably the most usually utilized GUI VBA controls.


In the VBA GUI control,

Click on the engineering tab

Click on the Insert Dropdown button

You will get the accompanying drop-down board


VBA Operators

On the off chance that you float the mouse over VBA structure controls, the name of the control will show up as displayed beneath

In Excel, VBA (Visual Basic for Applications) controls allow you to enhance the functionality of your spreadsheets by creating interactive forms and user interfaces. There are two primary types of VBA controls: VBA Form Controls and ActiveX Controls. Let's explore each type and understand how they can be used.

VBA Form Controls:

VBA Form Controls are a set of built-in controls provided by Excel. They are relatively straightforward to use and are suitable for most basic tasks. Here are some common VBA Form Controls:

1.     Button Control: A button allows you to trigger VBA code when it is clicked. It's commonly used for running macros or performing specific actions within a worksheet.

2.     Checkbox Control: Checkboxes enable users to make multiple selections in a list. They are often used in forms to indicate options or preferences.

3.     Option Button Control: Option buttons, also known as radio buttons, allow users to select one option from a group of choices. They are typically used when only one selection is allowed.

4.     List Box Control: List boxes provide a list of items from which users can select one or more options. They are useful for creating dropdown lists or selecting multiple items.

5.     Combo Box Control: Combo boxes combine a text box and a list box, allowing users to either select an item from the list or type in their choice. They are commonly used for dynamic data selection.

6.     Label Control: Labels are used to display static text or descriptions on a worksheet. They do not trigger VBA code but provide information to users.

ActiveX Controls:

ActiveX Controls offer more advanced and customizable features compared to VBA Form Controls. They are versatile and can be used to create complex user interfaces. Here are some common ActiveX Controls:

1.     ActiveX Command Button: Similar to the VBA Form Control button, an ActiveX command button can execute VBA code when clicked. However, it offers more extensive customization options, such as event handling and design.

2.     ActiveX Text Box: ActiveX text boxes are used for data input. They allow users to enter text or numbers and are often used in user forms.

3.     ActiveX Check Box: ActiveX checkboxes work similarly to VBA Form Control checkboxes but provide more customization options for appearance and behavior.

4.     ActiveX Combo Box: ActiveX combo boxes offer enhanced features compared to the Form Control combo box. You can customize their appearance and behavior extensively.

5.     ActiveX List Box: ActiveX list boxes allow you to create interactive lists with advanced formatting options.

6.     ActiveX Option Button: These are more customizable versions of radio buttons, providing greater control over appearance and behavior.

7.     ActiveX Label: ActiveX labels are similar to VBA Form Control labels but offer more formatting options and can be used for interactive purposes.

Using VBA with Controls:

Regardless of whether you use VBA Form Controls or ActiveX Controls, you can write VBA code to interact with and manipulate these controls. You can assign macros to control events (e.g., button clicks) or use VBA to read user input from text boxes and other controls.

VBA controls in Excel empower you to create user-friendly and interactive spreadsheets, making data input and analysis more efficient and user-centric. Understanding when to use VBA Form Controls or ActiveX Controls depends on your specific needs and the complexity of your Excel project.

Adding GUI controls to a bookkeeping sheet

We will currently add an order button to our exercise manual, for that we need to

Click on Insert drop down

Click on Command Button (ActiveX Control) as displayed in the picture above

Drag and draw the button on the sheet

VBA Operators

Setting GUI control properties

We will presently set the inscription of the button to something more expressive


VBA Operators

Right snap on the equivalent button that we have recently made

Select properties menu

You will get the accompanying properties window

VBA Operators

Set the name property to the button. The name property is utilized to recognize the control in the code window. btn is the prefix for the button.

Set the Caption property to Click Me. The clients will see on the button the text in the subtitle property.

Close the window when you are finished.

Click here to find out more

You will come by the accompanying outcomes.


VBA Operators

The most effective method to utilize ActiveX control in VBA

In this part of ActiveX control Excel, we will perceive how to consolidate the 'command-click' button in VBA and execute a program utilizing the button.

Nursery Teacher Training

Stage 1) In this step, click the choice "embed button" from the Active X Control. Then select the order button choice from it.

Stage 2) To embed "clickcommandbutton1" drag the mouse cursor to the Excel sheet.

Stage 3) Then right snap on the order button and select the choice "View Code".

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

Find out more

In code, we characterized the range A1 to A10.

This reach characterizes the cell number in the Excel sheet from "A1 to A10."

Then, we characterize the string "Guru99 VBA Tutorial" in code

Experience the difference

Along these lines, when you click on the "order button" it succeeds. It would be ideal for it to print "Guru99 VBA Tutorial" in the cell range "A1 to A10."

Stage 5) In the following stage, save the code record and afterward leave the supervisor. To get back to the Excel document click the Excel sheet symbol What is VBA? on the super left.

Stage 6) In Excel sheet, you will see Design Mode tab is on. Ensure it is "OFF" or, in all likelihood your code won't work.

Stage 7) When plan mode is off, there will be no green feature on it. Presently you can Click on the order button.

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

Essential

Arrange Microsoft Excel

As we see in past instructional exercise, ensure your strip will have "Designer" symbol as displayed here.

See also here -->>

VBA Operators

Presently, rename sheet1 to "VBA Operator" as displayed in screen-shot underneath. Save the exercise manual in an Excel Macro-Enabled Workbook (*.xlsm) document augmentation. (see the picture underneath).

From that point forward, click on Save button.

Read More :

Project Management Life Cycle Phases
Workflow Monitor in Informatica
Best Project Management Software
Excel VBA Subroutine: How to Call Sub in VBA with Example
Workflows In Informatica

Featured Universities

Mahatma Gandhi University

Location: Soreng ,Sikkim , India
Approved: UGC
Course Offered: UG and PG

MATS University

Location: Raipur, Chhattisgarh, India
Approved: UGC
Course Offered: UG and PG

Kalinga University

Location: Raipur, Chhattisgarh,India
Approved: UGC
Course Offered: UG and PG

Vinayaka Missions Sikkim University

Location: Gangtok, Sikkim, India
Approved: UGC
Course Offered: UG and PG

Sabarmati University

Location: Ahmedabad, Gujarat, India
Approved: UGC
Course Offered: UG and PG

Arni University

Location: Tanda, Himachal Pradesh, India.
Approved: UGC
Course Offered: UG and PG

Capital University

Location: Jhumri Telaiya Jharkhand,India
Approved: UGC
Course Offered: UG and PG

Glocal University

Location: Saharanpur, UP, India.
Approved: UGC
Course Offered: UG and PG

Himalayan Garhwal University

Location: PG, Uttarakhand, India
Approved: UGC
Course Offered: UG and PG

Sikkim Professional University

Location: Sikkim, India
Approved: UGC
Course Offered: UG and PG

North East Frontier Technical University

Location: Aalo, AP ,India
Approved: UGC
Course Offered: UG and PG