How To Add Source To Combobox In Excel

Contextures

Excel UserForm With Comboboxes

To make it easier for users to enter data in a workbook, you can create an Excel UserForm. In this example, combo boxes are added, to create drop down lists where you can select items, instead of typing them.

Writer: Debra Dalgleish

Introduction to UserForms

To create a UserForm requires some programming, and yous can meet the steps in the videos shown below. For the written instructions, look beneath the videos.

In this example, inventory data is stored on a hidden worksheet, where it is protected from accidental impairment or deletion. Users enter inventory data by opening the UserForm, filling in the boxes, and clicking a button.

Videos: Create a User Grade

To see the steps for creating a UserForm, please watch these three short video tutorials. The written instructions are below the video.

In the videos, Excel 2003 is used, and the same steps can be followed in subsequently versions. When saving the file in Excel 2007 or later, save as a macro-enable file type.

Creating a UserForm - Office i

In part 1, you'll come across how to create a blank Userform. Then you'll proper noun the UserForm, and adjacent you'll add text boxes and labels.

Users volition be able to type data into the text boxes. Labels are added beside the text boxes, to describe what users should enter into the text box

Creating a UserForm - Office 2

In Part 2, you'll learn how to add buttons and a championship on the UserForm.

With buttons on the UserForm, a user can click to make something happen. For example, click a push after inbound data in the text boxes, when you're set up to move the data to the worksheet storage area.

Creating a UserForm - Part 3

In Part 3, you'll learn how to add VBA code to the controls, and you'll see how to test the UserForm.

The VBA lawmaking runs when a specific issue occurs, such equally clicking a push button, or entering a combo box. In this case, the user will click a push, and the VBA code will move the data to the worksheet storage surface area.

Creating a UserForm - Part 4

In Part 4, you lot'll see the code that fills the items in the combo boxes.

Fix up the worksheet

In this case, a parts inventory is stored on a hidden worksheet.

Open up a new workbook
Double-click on the sheet tab for Sheet1
Type: PartsData
Printing the Enter key
In cells A1:D1, enter the headings for the parts inventory database, every bit shown at right.
Cull File | Save, and salvage the workbook. In this instance, the file has been named PartsLocDB.xls.
Note: In Excel 2007 and afterward versions, save the file as a macro-enabled file type.

A

B

C

D

one

PartID

Location

Date
Qty

2

12345

Store 001

3/3/2015

87

3

Create a UserForm

UserForms are created in the Visual Basic Editor.

To open the Visual Basic Editor, hold the Alt central, and printing the F11 key
Cull View | Project Explorer, to see a list of projects. (Commonly, this is displayed at the left side of the VBE window.)
In the Projection Explorer, select the PartLocDB project.
From the carte bar, choose Insert | UserForm
A bare UserForm appears, and the ToolBox should open. (If the ToolBox doesn't appear, choose View | Toolbox)

Proper name the UserForm

To open the Properties window, printing the F4 key
In the Properties window, double-click on the Name -- UserForm1, at the pinnacle right of the window.
Blazon:frmPartLoc
and printing the Enter key
The course proper noun will change in the Project Explorer, but the course notwithstanding shows UserForm1 in its title bar.
In the Properties window, double-click on the Caption property-- UserForm1.
Type:Parts Inventory
and press the Enter key
The title bar will display the new caption.

Add together a Combobox to the UserForm

The objects on a UserForm, such every bit buttons, and textboxes, are chosen controls. To allow users to enter data, yous can add textbox controls to the Excel UserForm, with label controls to describe them.

In the Toolbox, click on the ComboBox button.
On the UserForm, click most the pinnacle centre, to add together a standard sized combobox.
With the new combobox selected, double-click on the Proper name property in the Properties window.
Type:cboPart
and press the Enter key
(optional) Set the ColumnCount property to 2, if you want to encounter the part name listed with each part number.
Click on an empty office of the Excel UserForm, to select the Excel UserForm and to display the Toolbox.

Add a Label to the UserForm

To help users enter data, you can add together labelS to depict the controls, or to brandish instructions.

In the Toolbox, click on the Label button.
On the UserForm, click to the left of the combobox, to add together a standard sized label.
With the new label selected, double-click on the Caption belongings in the Properties window.
Type:Part
and press the Enter key
If necessary, you lot tin resize the label, and so it doesn't cover the combobox -- point to the handle on its right border, and drag to the left.
Click on an empty function of the UserForm, to select the UserForm and to brandish the Toolbox.

Add remaining boxes and labels

Repeat the higher up steps to add together:

a combobox named cboLocation, with a label Location
a textbox named txtDate, with a label Date
a textbox named txtQty, with a label Quantity

Excel UserFormTextboxes

If the textboxes are non aligned, you can align them:

Click on the first combobox
Concur the Ctrl key, and click on the remaining boxes
Choose Format | Marshal | Lefts
Click on an empty function of the UserForm, to select the UserForm and to display the Toolbox.

Add Buttons to the UserForm

To let users to perform an action, y'all can add together command buttons to the user form. This class has a button to add data to the database, and a button to shut the form.

In the Toolbox, click on the CommandButton button.
On the UserForm, click at the bottom left, to add together a standard sized CommandButton.
With the new CommandButton selected, double-click on the Proper noun property in the Properties window.
Type:cmdAdd
and press the Enter fundamental
With the new CommandButton selected, double-click on the Caption belongings in the Properties window.
Type:Add this part
and press the Enter key
Click on an empty part of the UserForm, to select the UserForm and to display the Toolbox.
Repeat the higher up steps to add together a CommandButton named cmdClose, with a label Close
If required, you can reposition the buttons by dragging them to a new location on the UserForm.

Add together code to the buttons

To make the buttons perform an action, yous create lawmaking that runs when the button is clicked.

Add code to the cmdAdd button

Select the cmdAdd push button
On the Menu bar, cull View | Code.
This creates a procedure, where you tin can add your lawmaking.
Where the cursor is flashing, enter the following code:

Private Sub cmdAdd_Click() Dim lRow As Long Dim lPart Equally Long Dim ws As Worksheet Prepare ws = Worksheets("PartsData")  'find first empty row in database lRow = ws.Cells.Discover(What:="*", SearchOrder:=xlRows, _      SearchDirection:=xlPrevious, LookIn:=xlValues).Row + one  lPart = Me.cboPart.ListIndex  'cheque for a part number If Trim(Me.cboPart.Value) = "" Then   Me.cboPart.SetFocus   MsgBox "Delight enter a office number"   Exit Sub End If  'copy the data to the database 'use protect and unprotect lines,  '     with your password '     if worksheet is protected With ws '  .Unprotect Password:="countersign"     .Cells(lRow, 1).Value = Me.cboPart.Value   .Cells(lRow, ii).Value = Me.cboPart.List(lPart, ane)   .Cells(lRow, 3).Value = Me.cboLocation.Value   .Cells(lRow, 4).Value = Me.txtDate.Value   .Cells(lRow, v).Value = Me.txtQty.Value '  .Protect Password:="password" End With  'articulate the data Me.cboPart.Value = "" Me.cboLocation.Value = "" Me.txtDate.Value = Format(Date, "Medium Date") Me.txtQty.Value = ane Me.cboPart.SetFocus  End Sub

On the Menu bar, choose View | Object, to return to the UserForm.

Add code to the cmdClose push

Select the cmdClose button
On the Menu bar, cull View | Code.
Where the cursor is flashing, enter the following code:

Individual Sub cmdClose_Click()   Unload Me Terminate Sub

On the Menu bar, choose View | Object, to render to the UserForm.

To allow users to shut the grade by pressing the Esc key:

Select the cmdClose button
In the Backdrop window, change the Abolish property to True

To forestall users from closing the course by clicking the Ten button

When the UserForm is opened, there is an Ten at the meridian correct. In improver to using the Close Course push, people will be able to shut the form by using the X. If you want to prevent that, follow these steps.

Right-click on an empty function of the UserForm
Choose View | Code
From the Procedure dropdown, at the top right, choose QueryClose
Where the cursor is flashing, paste the highlighted code from the post-obit sample

Private Sub UserForm_QueryClose(Cancel As Integer, _   CloseMode As Integer)          If CloseMode = vbFormControlMenu Then     Cancel = True     MsgBox "Please use the Close Form push!"   Terminate If          End Sub

On the Menu bar, choose View | Object, to return to the UserForm.

Now, if someone clicks the X in the UserForm, they'll come across your message.

QueryClose code inl UserForm

Add Lists to ComboBoxes

To populate the combobox lists, dynamic named ranges will be created in the workbook. Then, code will add the range items to the comboboxes, when the UserForm is initialized.

Create Dynamic Named Ranges

In Excel, insert a worksheet named LookupLists
In columns A and B, enter Part IDs and Parts, as shown at right, and in cavalcade E, enter a listing of Locations.
Choose Insert | Name | Define
In the Proper noun box, type:PartIDList
In the Refers to box, type:
=Get-go(LookupLists!$A$2,0,0,COUNTA(LookupLists!$A:$A)-i,1)
Click Add
In the Name box, type: PartsLookup
In the Refers to box, type:
=Kickoff(PartIDList,0,0,,two)
Click Add
In the Proper name box, type: LocationList
In the Refers to box, type:
=OFFSET(LookupLists!$E$2,0,0,COUNTA(LookupLists!$East:$E)-1,1)

Add lawmaking to create the lists

To create the drop down lists, yous tin can loop through a list on the worksheet, as described below. Or, enter the listing's range name in the philharmonic box properties, as described on the Excel VBA ComboBox Match page.

In the VBE, select the UserForm, and cull View | Lawmaking.
From the Process dropdown, at the pinnacle correct, choose Initialize
Enter the following code. For detailed instructions on this lawmaking, encounter Excel VBA ComboBox Lists

Private Sub UserForm_Initialize() Dim cPart Equally Range Dim cLoc Equally Range Dim ws Every bit Worksheet Set up ws = Worksheets("LookupLists")  For Each cPart In ws.Range("PartIDList")   With Me.cboPart     .AddItem cPart.Value     .Listing(.ListCount - 1, i) = cPart.Offset(0, one).Value   End With Next cPart  For Each cLoc In ws.Range("LocationList")   With Me.cboLocation     .AddItem cLoc.Value   Stop With Next cLoc  Me.txtDate.Value = Format(Date, "Medium Date") Me.txtQty.Value = i Me.cboPart.SetFocus  Finish Sub

Test the UserForm

To test the form, you lot can run it from the VBE.

Click on an empty role of the UserForm, to select the UserForm and to display the Toolbox.
On the Menu bar, choose Run | Run Sub/UserForm.
In the Office textbox, click the arrow, and select a Office ID
Press the tab key to motion to the adjacent command.
When the boxes have been filled in, click the 'Add together this role' button.
Click the 'Close grade' push, to return to the VBE.

If the tab lodge was incorrect (due east.g. when you pressed the tab key you lot moved to the incorrect textbox or button), yous can change information technology

Right-click on an empty part of the UserForm
Choose Tab Club
Select a command in the list, and click the Move Up or Motion Downwardly button
Click OK

Create a Button to open up the UserForm

To go far piece of cake for users to open the UserForm, you can add a push button to a worksheet.

Switch to Excel, and activate the PartLocDB.xls workbook
Double-click on the canvas tab for Sheet2
Type: Parts Data Entry
Press the Enter central
On the Drawing toolbar, click on the Rectangle tool (In Excel 2007 / 2010, utilize a shape from the Insert tab)
In the center of the worksheet, depict a rectangle, and format every bit desired.
With the rectangle selected, type:
Click here to add Function Information
Right-click on the rectangle border, and cull 'Assign Macro'
Click the New button
Where the cursor is flashing, type: frmPartLoc.Show
Go to the Excel window, and click the button, to open the UserForm.

NOTE: While the UserForm is open, yous won't be able to perform whatever other actions in Excel, such every bit entering data on the worksheet. You lot'll have to close the form starting time.
If you desire users to be able to perform other deportment in Excel while the form is open, change the above line to: frmPartLoc.Testify Imitation to turn off the Modal setting

Create a Pivot Table

To summarize the information in the parts inventory, y'all can create a pivot table.

Switch to Excel, and activate the PartLocDBCombo.xls workbook
Select the PartsData sail.
Create a dynamic range named PartsDatabase, with a Refers to formula:
=OFFSET(PartsData!$A$1,0,0,COUNTA(PartsData!$A:$A),v)
From the menu bar, choose Data | PivotTable and PivotChart Report (In Excel 2007 / 2010, apply the PivotTable command on the Insert tab)
Click Adjacent, and in the Source Data box, type: PartsDatabase
Click Adjacent, and click the Layout push button.
Elevate the PartID button and the Office button to the Row area.
Drag the Location button to the Column area
Drag the Qty button to the Information area
Click the Finish button
Name the pivot table worksheet, PartsInventory.

Create a Button to View the Pivot Table

To arrive piece of cake for users to refresh and view the pivot table, you can add a push button to a worksheet.

Switch to Excel, and activate the PartLocDB.xls workbook
Select the Parts Information Entry canvass
Add a rectangle with the text::
Click here to view Parts Inventory
Correct-click on the rectangle border, and cull 'Assign Macro'
Click the New button
Where the cursor is flashing, type the following code:

          On Error Resume Next   With Worksheets("PartsInventory")     .Activate     .PivotTables(1).PivotCache.Refresh   Stop With

Finish the Workbook

To stop the workbook, you lot tin hibernate the sail that contains the database.

Switch to Excel, and activate the PartLocDB.xls workbook
Select the PartsData sheet.
From the menu bar, choose Format | Sheet | Hide (In Excel 2007 / 2010, employ the Format commands on the Abode tab)
Delete all other sheets, except Parts Data Entry
Click the Click here to add together Part Information button, and enter your data into the database.
Close and save the workbook

Become the Sample File

Basic: Download the sample Excel UserForm file
Enhanced: Download the sample file with an enhanced Excel UserForm, with comboboxes