Search Contextures Sites

 

Excel -- UserForm With ComboBoxes

  1. Set up the the worksheet
  2. Create a UserForm
  3. Name the UserForm
  4. Add a Combobox to the UserForm
  5. Add a Label to the UserForm
  6. Add remaining boxes and labels
  7. Add buttons to the UserForm
  8. Add Code to the Buttons
  9. Add Lists to ComboBoxes
  10. Test the UserForm
  11. Create a button to open the UserForm
  12. Create a Pivot Table
  13. Create a button to view the Pivot Table
  14. Finish the workbook

UserForm

Download the zipped sample file

To make it easier for users to enter data in a workbook, you can create a UserForm.

In this example, a parts inventory is stored on a hidden worksheet, and summarized in a pivot table. Users enter data by opening the UserForm, filling in the boxes, and clicking a button.

 

 

Set up the worksheet

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

  1. Open a new workbook
  2. Double-click on the sheet tab for Sheet1
  3. Type: PartsData
  4. Press the Enter key
  5. In cells A1:E1, enter the headings for the parts inventory database, as shown at right.
  6. Choose File | Save, and save the workbook. In this example, the file has been named PartsLocDBCombo.xls.

 
A
B
C
D
E
1
PartID
 Part
Location
Date
 Qty
2
12345
 Door
Warehouse
8-Aug
15
3
          
 

Create a UserForm

UserForms are created in the Visual Basic Editor.

  1. To open the Visual Basic Editor, hold the Alt key, and press the F11 key
  2. Choose View | Project Explorer, to see a list of projects. (Usually, this is displayed at the left side of the VBE window.)
  3. In the Project Explorer, select the PartLocDB project.
  4. From the menu bar, choose Insert | UserForm
  5. A blank UserForm appears, and the ToolBox should open. (If the ToolBox doesn't appear, choose View | Toolbox)  

InsertUserForm

Name the UserForm

  1. To open the Properties window, press the F4 key
  2. In the Properties window, double-click on the Name -- UserForm1, at the top right of the window.
  3. Type:  frmPartLoc
    and press the Enter key
  4. The form name will change in the Project Explorer, but the form still shows UserForm1 in its title bar.
  5. In the Properties window, double-click on the Caption property-- UserForm1.
  6. Type:  Parts Inventory
    and press the Enter key
  7. The title bar will display the new caption. 

Name the form

Add a Combobox to the UserForm

The objects on a UserForm, such as buttons, and textboxes, are called controls. To allow users to enter data, you can add textbox controls to the form, with label controls to describe them.

  1. In the Toolbox, click on the ComboBox button.
  2. On the UserForm, click near the top centre, to add a standard sized combobox.
  3. With the new combobox selected, double-click on the Name property in the Properties window.
  4. Type:  cboPart
    and press the Enter key
  5. (optional) Set the ColumnCount property to 2, if you want to see the part name listed with each part number.
  6. Click on an empty part of the UserForm, to select the UserForm and to display the Toolbox. 

Textbox

Name

Add a Label to the UserForm

To help users enter data, you can add label controls to describe the controls, or to display instructions.

  1. In the Toolbox, click on the Label button.
  2. On the UserForm, click to the left of the combobox, to add a standard sized label.
  3. With the new label selected, double-click on the Caption property in the Properties window.
  4. Type:  Part ID
    and press the Enter key
  5. If necessary, you can resize the label, so it doesn't cover the combobox-- point to the handle on its right border, and drag to the left.
  6. Click on an empty part of the UserForm, to select the UserForm and to display the Toolbox. 

Label

Resize the label

Add remaining boxes and labels

Repeat the above steps to add:

  • a combobox named cboLocation, with a label Location
  • a textbox named txtDate, with a label Date
  • a textbox named txtQty, with a label Quantity 
If the textboxes are not aligned, you can align them:
  1. Click on the first combobox
  2. Hold the Ctrl key, and click on the remaining boxes
  3. Choose Format | Align | Lefts
  4. Click on an empty part of the UserForm, to select the UserForm and to display the Toolbox.   

 

Remaining Textboxes

Add Buttons to the UserForm

To allow users to perform an action, you can add command buttons to the user form. This form has a button to add data to the database, and a button to close the form.

  1. In the Toolbox, click on the CommandButton button.
  2. On the UserForm, click at the bottom left, to add a standard sized CommandButton.
  3. With the new CommandButton selected, double-click on the Name property in the Properties window.
  4. Type:  cmdAdd
    and press the Enter key
  5. With the new CommandButton selected, double-click on the Caption property in the Properties window.
  6. Type:  Add this part
    and press the Enter key
  7. Click on an empty part of the UserForm, to select the UserForm and to display the Toolbox.
  8. Repeat the above steps to add a CommandButton named cmdClose, with a label Close
  9. If required, you can reposition the buttons by dragging them to a new location on the UserForm. 

 

 

CommandButton

 

Move CommandButtons

Add Code to the Buttons

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

Add code to the cmdAdd button

  1. Select the cmdAdd button
  2. On the Menu bar, choose View | Code. 
  3. This creates a procedure, where you can add your code.
  4. Where the cursor is flashing, enter the following code:
Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")

'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

lPart = Me.cboPart.ListIndex

'check for a part number
If Trim(Me.cboPart.Value) = "" Then
  Me.cboPart.SetFocus
  MsgBox "Please enter a part number"
  Exit Sub
End If

'copy the data to the database
With ws
  .Cells(lRow, 1).Value = Me.cboPart.Value
  .Cells(lRow, 2).Value = Me.cboPart.List(lPart, 1)
  .Cells(lRow, 3).Value = Me.cboLocation.Value
  .Cells(lRow, 4).Value = Me.txtDate.Value
  .Cells(lRow, 5).Value = Me.txtQty.Value
End With

'clear the data
Me.cboPart.Value = ""
Me.cboLocation.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.cboPart.SetFocus

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

 

 

AddCode

 

 

Add code to the cmdClose button

  1. Select the cmdClose button
  2. On the Menu bar, choose View | Code. 
  3. Where the cursor is flashing, enter the following code:
Private Sub cmdClose_Click()
  Unload Me
End Sub
  1. On the Menu bar, choose View | Object, to return to the UserForm.

To allow users to close the form by pressing the Esc key:

  1. Select the cmdClose button
  2. In the Properties window, change the Cancel property to True  

 

 

 

 

 

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

  1. In Excel, insert a worksheet named LookupLists
  2. In columns A and B, enter Part IDs and Parts, as shown at right, and in column E, enter a list of Locations. 
  3. Choose Insert | Name | Define
  4. In the Name box, type:  PartIDList
  5. In the Refers to box, type:
    =OFFSET(LookupLists!$A$2,0,0,COUNTA(LookupLists!$A:$A)-1,1)
  6. Click Add
  7. In the Name box, type: PartsLookup
  8. In the Refers to box, type:
    =OFFSET(PartIDList,0,0,,2)
  9. Click Add
  10. In the Name box, type: LocationList
  11. In the Refers to box, type:
    =OFFSET(LookupLists!$E$2,0,0,COUNTA(LookupLists!$E:$E)-1,1)

 

 

 

 

 

 

Add code to create the lists

  1. In the VBE, select the UserForm, and choose View | Code. 
  2. From the Procedure dropdown, at the top right, choose Initialize
  3. Enter the following code:
Private Sub UserForm_Initialize()
Dim cPart As Range
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

For Each cPart In ws.Range("PartIDList")
  With Me.cboPart
    .AddItem cPart.Value
    .List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
  End With
Next cPart

For Each cLoc In ws.Range("LocationList")
  With Me.cboLocation
    .AddItem cLoc.Value
    .List(.ListCount - 1, 1) = cLoc.Offset(0, 1).Value
  End With
Next cLoc

Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.cboPart.SetFocus

End Sub

 

Test the UserForm

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

  1. Click on an empty part of the UserForm, to select the UserForm and to display the Toolbox.
  2. On the Menu bar, choose Run | Run Sub/UserForm.
  3. In the Part textbox, type: 12345
  4. Press the tab key to move to the next textbox.
  5. When the textboxes have been filled in, click the 'Add this part' button.
  6. Click the 'Close form' button, to return to the VBE.

If the tab order was incorrect (e.g. when you pressed the tab key you moved to the wrong textbox or button), you can change it

  1. Right-click on an empty part of the UserForm
  2. Choose Tab Order
  3. Select a control in the list, and click the Move Up or Move Down button
  4. Click OK 

 

UserForm

 

Create a Button to open the UserForm

To make it easy for users to open the UserForm, you can add a button to a worksheet.

  1. Switch to Excel, and activate the PartLocDB.xls workbook
  2. Double-click on the sheet tab for Sheet2
  3. Type: Parts Data Entry
  4. Press the Enter key
  5. On the Drawing toolbar, click on the Rectangle tool
  6. In the centre of the worksheet, draw a rectangle, and format as desired.
  7. With the rectangle selected, type:
    Click here to add Part Information
  8. Right-click on the rectangle border, and choose 'Assign Macro'
  9. Click the New button
  10. Where the cursor is flashing, type: frmPartLoc.Show
    Note:     If you are using Excel 2000, or later version, and want users to be able to do other things while the form is open, change the above line to:
    frmPartLoc.Show False 

To prevent users from closing the form by clicking the X button:

  1. Right-click on an empty part of the UserForm
  2. Choose View | Code
  3. Scroll to the bottom of the existing code, and enter the following code:
Private Sub UserForm_QueryClose(Cancel As Integer, _
  CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please use the button!"
  End If
End Sub
  1. On the Menu bar, choose View | Object, to return to the UserForm.

 

 

 

 

 

OpenUserForm

 

Create a Pivot Table

To summarize the data in the parts inventory, you can create a pivot table.

  1. Switch to Excel, and activate the PartLocDBCombo.xls workbook
  2. Select the PartsData sheet.
  3. Create a dynamic range named PartsDatabase, with a Refers to formula:
    =OFFSET(PartsData!$A$1,0,0,COUNTA(PartsData!$A:$A),5)
  4. From the menu bar, choose Data | PivotTable and PivotChart Report
  5. Click Next, and in the Source Data box, type: PartsDatabase
  6. Click Next, and click the Layout button. 
  7. Drag the PartID button and the Part button to the Row area.
  8. Drag the Location button to the Column area
  9. Drag the Qty button to the Data area
  10. Click the Finish button 
  11. Name the pivot table worksheet, PartsInventory. 

 

 

 

Create a Button to View the Pivot Table

To make it easy for users to refresh and view the pivot table, you can add a button to a worksheet.

  1. Switch to Excel, and activate the PartLocDB.xls workbook
  2. Select the Parts Data Entry sheet
  3. Add a rectangle with the text::
    Click here to view Parts Inventory
  4. Right-click on the rectangle border, and choose 'Assign Macro'
  5. Click the New button
  6. Where the cursor is flashing, type the following code:
  On Error Resume Next
  With Worksheets("PartsInventory")
    .Activate
    .PivotTables(1).PivotCache.Refresh
  End With

 

 

 

OpenUserForm

 

Finish the Workbook

To finish the workbook, you can hide the sheets that contains the database and lookup lists.

  1. Switch to Excel, and activate the PartLocDB.xls workbook
  2. Select the PartsData sheet.
  3. From the menu bar, choose Format | Sheet | Hide
  4. Hide the LookupLists sheet
  5. Delete any unused sheets
  6. Click the Click here to add Part Information button, and enter your data into the database. 
  7. Close and save the workbook

Download the sample file

 

 

 

 

 
       Home     Excel Tips     Excel Files      Blog    Contact

RSS Feed

Privacy Policy

 

 

The Excel Store

 
Last updated: May 29, 2009 4:00 PM