Vba code create combo box in excel userform download#
Download the Project Workbook – Excel Macro File.Assign Data Entry Userform macro to a Shape on the Worksheet.How we are going to develop this Data Entry Userform (The KEY steps).SInsertQuery = "INSERT INTO Employee (EmployeeName, Designation, Gender, Misc1, Misc2, Country) " & _ SConnString = "PROVIDER = .12.0 Data Source = E:\Employee.accdb Persist Security Info=False" Sub SaveData(sEmpName As String, sDesig As String, sGender As String, sMisc1 As String, _ ' Procedure to save data extracted from the dynamically created fields. MsgBox "There are no categories in the list.", vbCritical + vbOKOnly Rs.Open sQuery, m圜onn, adOpenKeyset, adLockOptimisticĬmb.AddItem rs.Fields(1).Value ' Fill the combo box. sQuery = "SELECT *from " ' Read data in sheet name Country. "DBQ=" & ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name ' Fill combo box with data extracted from Sheet2 (or sheet name Country). Private Sub fillCombo(cmb As MSForms.ComboBox) IIf(chkMisc1 "", chkMisc1.Value, ""), IIf(chkMisc2 "", chkMisc2.Value, ""), cmbCountry.Value) ' Click event for the dynamically created Submit button.Ĭall SaveData(txtName.Text, txtDesig.Text, IIf(optFemale, "Female", "Male"), _ ' Fill the combo box with a list of countries dynamically. Public Sub Init(myForm As UserForm1, fraMain As MSForms.Frame) Public WithEvents btSubmit As MSForms.CommandButtonįraContainer.BorderStyle = fmBorderStyleNone In the Reference window, find and select Microsoft ActiveX Data Objects 6.1 Library (or any other version). Therefore, you must add the ADO library reference in your application.įrom the top menu in your VBA editor, click Tools and choose References.
I am using ADO connection object to set a connection and fetch records from Sheet2 (for the combo box). Instead, I’ll add the list of countries in Sheet2 in my worksheet (I have named Sheet2 as Country), and extract and fill this data to the ComboBox, at run time. Since this list will have many values, I don’t want to hardcode the values. The UserForm in this example, has a ComboBox (a dropdown list), which has a list of Countries. Add a Module to Create ActiveX Data Objects
The Frame will serve as the main or parent container for all the controls that we’ll create at run time. This is the only control we’ll actually add at design time.
Next, open Project Explorer window, right click the project to Insert a UserForm.įrom the ToolBox, drag and drop a Frame on to the UserForm. You can open the editor by right clicking the Sheet1 tab (or any tab at the bottom of your worksheet) and choose View Code option or simply press Alt+F11 keys. To add a UserForm, you will have to open the VBA editor. Open an Excel file and save the file as Macro or in. Name the table as Employee and add few fields to the table. Open Ms-Access, and from the top menu, find Create and choose Table option. Since, I’ll be saving the data in the UserForm in an Ms-Access table, I’ll first create the table. The most important part of this example is that it creates an ActiveX Command Button with Events, at run time, so you can save the data. It’s a simple data entry form, where a user can enter details about an Employee (its like an Employee master) and save the data in a database (Ms-Access). Related Post: Excel VBA – Loop Through All Textboxes in UserForm and Clear the Values The example here does not cover or create all the ActiveX Controls that are available in the toolbox, but it creates some of the most important controls (like the textbox, combo, checkbox etc.) dynamically and adds it to a Frame inside the UserForm.