Add New ComboBox Items in Excel UserForm

If you want to enter data in an Excel worksheet, while keeping the data sheet hidden, you can create an Excel UserForm.

I’ve updated my sample file, so you can now add new parts, while entering data. It’s almost working the way it should, but I’m stuck on one step, so if you have a solution, please let me know!

[Update: Problem solved with a workaround — see below.]

Select a Part from the ComboBox Drop Down List

In the sample file, you can click the Add Parts Information button, to open the UserForm.

Then, select a Part ID from the combo box drop down list.

userformcomboadd02

The Parts List

On another sheet in the workbook, there are two lists – Location, and Parts. These are dynamic named ranges, and will expand automatically, as new items are added to the lists.

userformcomboadd01

Add a New Part to the List

In the latest version of the sample file, you can add new parts to the list, while you are entering data in the UserForm.

  1. If the Part ID that you want is not in the list, type it into the Part ID combo box.
  2. When you press the Tab key, to move to the next control, a Part Description text box will appear.
  3. Enter the description, then fill in the rest of the data.
  4. Click the Add This Part button

userformcomboadd05

Select the New Part

After you click the Add This Part button, the new item is added to the Parts List, and the Parts list is sorted A-Z.

userformcombo06

The new item now appears in the Part ID combo box drop down list.

userformcomboadd04

SetFocus Problem

My goal was to have the Part Description activated, as soon as it was made visible. However, the VBA code wouldn’t cooperate, so I’ve commented out this line:

Me.txtPartDesc.SetFocus

If you have a solution for getting that line to work, please share it in the comments, or send me an email. I’d appreciate it!

Update: Thanks to JeanMarc, Jon and Dave, the tab order is working now.You can see their suggestions in the comments below.

  • Instead of being hidden, the Parts Description textbox moves to the far right, so it’s not in the visible part of the form, then moves back when needed.
  • To keep the tab key from stopping on the “off form” textbox, its position is checked. If it’s at the far right, go to the next control.

Download the Sample File

To see the sample file, and check the Excel VBA code, you can download the file from my Contextures website. On the Sample Excel Files page, in the UserForm section, look for UF0017 – Parts Database with Updateable Comboboxes

The file is available in Excel 2007/2010 or Excel 2003 format, and zipped. It contains macros, so enable those if you want to test the UserForm.

_____________________

You may also like...

17 Responses

  1. Jon says:

    I couldn’t figure out how to do any workarounds. It looks like a bug on Microsoft’s side.

    Here’s what someone else did. But the combinations I tried nothing worked. It might be because the text box is hidden and then shown again, and maybe showing the form again with the text box set to visible in the load event? But doing that seems to crash the program. You might just be SOL on this one :\

  2. JeanMarc says:

    Hi Debra,
    Here’s a work around that seems to work.
    Don’t hide the controls but change their position .
    In other words instead of :

    ‘Me.txtPartDesc.Visible = False
    ‘Me.lblPartDesc.Visible = False
    Me.txtPartDesc.Left = 500
    Me.lblPartDesc.Left = 500

    and :

    ‘Me.txtPartDesc.Visible = True
    ‘Me.lblPartDesc.Visible = True
    Me.txtPartDesc.Left = 78
    Me.lblPartDesc.Left = 18

    If you change your UserForm_Initialize() and your cboPart_AfterUpdate() accordingly it works!

    • Hi JeanMarc,
      Thanks, I liked your idea, and tested it. It works well when adding a new part — the part description is selected after exiting the Part ID combo box.

      But, when adding an existing part, it tabs to the Part Description, which is off to the side, and another tab is required, to get to the Location combo.

      So, I tried changing the txtPartDesc.Enabled property, and that had the same result as changing its Visible property — it messes up the tab order.

      Sigh.

      • JeanMarc says:

        I realized after posting my comment that there was a flaw in my suggestion (for the existing parts)
        I’m happy to see that there is a solution to it !!

  3. Dave says:

    That’s pretty ingenious!

    Without any testing at all, you may want to toggle the tabindex and tabstop stuff, too.

    I’m not sure if that way off the userform textbox will still get focus if the tab key is hit.

    I’m sure Debra can share here results, right sir?

    (That cracks me up!)

    • @Dave, yep, it still gets focus on the tab key. It was a good idea though!

      • Dave says:

        Did you try toggling the .tabstop property in your code?

        Or did that break it like the .visible property did?

        • @Dave, yes, I tried the tabstop property too. That broke it too.

          But, I just tried JeanMarc’s idea again, and added code to the Enter event for the Part Description. This seems to work:

          Private Sub txtPartDesc_Enter()
          If Me.txtPartDesc.Left = 500 Then
            Me.cboLocation.SetFocus
          End If
          
          End Sub
  4. Jon says:

    Could you catch the control event when it is selected, test its location and then bounce it to the next control if it is not “active?”

  5. Jeff Weir says:

    Hmm…I’m getting the following error message on frmParts.Show :
    Method ‘Range’ of object ‘_Worksheet’ failed

  6. Terry says:

    When it auto sorts Col A (PartD) it does not seem to include Col B (PartDesc). What do I need to add that includes both columns when I sort?

    Thanks!!

  7. Terry says:

    I think I have found it.

    =OFFSET(LookupLists!$E$2:$F$2,0,0,COUNTA(LookupLists!$E:$F)-1,2)

    Thanks!!

  8. Bob says:

    I’m completely new to VBA and have puzzled over this for several days now. I’d like to adapt this model to a depreciation schedule. How do I make the following adjustments

    1. In ‘LookupLists’, how do I add a column to the left of Location named ‘Location ID’? (I know how to add in Excel, but not in VBA) How do I add the same to VBA so that it appears correctly in ‘PartsData’?
    2. How do I change the VBA code to reflect different column headings and a slightly different column order in ‘PartsData’? The following refers
    a. Col. A ‘Location ID No.’ (new column & heading)
    b. Col. B ‘Location’ (current heading)
    c. Col. C ‘Description’ (current heading ‘PartDesc’)
    d. Col. D ‘Useful Life’ (current heading ‘Part ID’)

    Thanks for your consideration and help.

  9. Bernard says:

    Hi,

    I’m a newbeie and i’m trying to figure all this out…

    1. If i want to return a value(s)from a difference WS, where and what do I insert in the formula below or do i use another formula…

    =INDEX(A2:C7,MATCH($A$16,C2:C7,0),1) (Get Report ID “?” form another WS?

    2. Once it searches and find all the rows that match the criteria, how do i have it Displayed it in another WS?

    Many thanks for any help you can provide.

    Bernard

Leave a Reply to Jon Cancel reply

Your email address will not be published. Required fields are marked *