' THE CHANGE EVENT OF THE FIRST COMBO TO POPULATE THE SECOND COMBO BOX. Click OK.įrom the Project Explorer, double click the ThisWorkbook object and write the below code. In the references box, find Microsoft ActiveX Data Objects 6.1 Library (or any current version). To add reference to your project, click the Tools menu on the top and choose References… option. In this example, the database is the Excel file itself. This will provide the necessary classes, properties and methods to connect database objects.
Add a Reference of ActiveX Data Objectīefore you write any code, you will need to add a Reference of ActiveX Data Object or ADO. Press Alt+F11 keys to open Visual Basic IDE. Once you have designed the form and added the data to the second sheet (Books), its time to write the VBA code to cascade the combo box. The VBA (Macro) Code to Cascade the Combo Box This will show the price for the selected book of a particular category. Note: I have also added a label control and named it lblPrice, just next to the second combo. It will simply clear the selected values in the combo boxes. I have named the button control as cmbClear. I have named the combo boxes as cmbCategories and cmbBooks respectively. Finally, drag and drop the controls that you wish to add on the sheet. To add these controls on your sheet, choose the Developer menu on the top, find and click Insert option. I have also added a button to clear (or reset) the combos. The sheet1 is a form with two combo boxes.
I’ll extract data from the second sheet and populate the combos with the data.Ĭreate a Form in Excel using ActiveX Controls I am actually using the second sheet as a database. The first is sheet1 and the second is Books. Related: How to Capture Filter Change Event in Excel using VBA – Trigger a Macro Function on Filter Change Create a Database in Excel Note: If you have worked with Visual Basic 6, you will find this example simple and easy to understand as I am using ActiveX Data Object (or ADO) classes and methods. Related Post: Fill Combo Box in Excel with values from SELECT Dropdown using VBA After you select a category from the first combo box (for example Science), the second combo populates values (a list of science related books) based on the category. The first has a list of book Categories and the second is the cascading combo box, which is empty when the excel file opens. As you can see in the above image, I have two combo boxes.
0 kommentar(er)
