Link to home
Start Free TrialLog in
Avatar of Adam Elsheimer
Adam ElsheimerFlag for Germany

asked on

Add a second combobox VBA Excel

I would like add one more combobox to my userform.

The entry field ZG BKZ as shown below should be a combobox. The list is in Sheet "LookupList". The Userform is in sheet "Neukunden".

I am not able to do this myself. I produce error messages.


User generated image
Any assistance will be greatly appreciated.

Thank you

Regards,

Adam
Sample_Userform.xlsm
ASKER CERTIFIED SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Adam Elsheimer

ASKER

Hi Shums, many thanks for your very fast and uncomplicated help. Works great.
You're always Welcome Adam!
Just change this line as well, I forgot in my previous post:
.Cells(lRow, 6).Value = Me.TextBox1.Value
to
.Cells(lRow, 6).Value = Me.cboKM.Value
Thanks, I just changed it. It works great.
Adam, you really ought to wait to see if any other suggestions are given.

Your form's code could be written much more efficiently.

I would first tidy up the Lookup table by removing the two empty columns, then format it as a Table.  Now you can do away with the dynamic named ranges and load the comboboxes more efficiently.

Loading the ComboBoxes (this also applies to ListBoxes) is best done using the List Property. AddItem adds one item at a time and the longer the list of items the more it will impact on the speed of your code. Also, using a Loop is well known to slow code down, the List Property enables you to avoid such Loops. In the attached example I have reduced the code to populate the ListBoxes to this

Private Sub UserForm_Initialize()
    Set oTbl = wksLookupLists.ListObjects(1)
    With Me
        .cboPart.List = oTbl.DataBodyRange.Value
        .cboLocation.List = oTbl.ListColumns(3).DataBodyRange.Value
        .cboKM.List = oTbl.ListColumns(4).DataBodyRange.Value
        .txtDate.Value = Format(Date, "Short Date")
        .txtQty.Value = 1
        .cboPart.SetFocus
    End With
End Sub

Open in new window


You have assigned Code names to your worksheets so it is unnecessary to Declare a Variable for the worksheets and then set a value to that variable. The code name can be used like this

Set oTbl = wksPartsData.ListObjects(1)

Open in new window


Since you have formatted your data as a Table then it makes sense to use code to populate it that uses the Tables Properties, in which case you do not need to find the next empty row, but simply add a new row to the Table at the bottom. A row in a Table is a ListRow. You then simply add the data using the new row.
Adam_Sample_Userform--Roy-.xlsm
Thank you very much for your solution.

Please forgive my impatience. I don't know much about coding and efficiency.

It is a excellent and userfriendly solution. I can use it as a template for other userforms if I need more comboboxes in the userform.

Thank you and Shums for your help.
Pleased to help.