Adam Elsheimer
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.
Any assistance will be greatly appreciated.
Thank you
Regards,
Adam
Sample_Userform.xlsm
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.
Any assistance will be greatly appreciated.
Thank you
Regards,
Adam
Sample_Userform.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
.Cells(lRow, 6).Value = Me.TextBox1.Value
to
.Cells(lRow, 6).Value = Me.cboKM.Value
ASKER
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
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
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
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
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)
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
ASKER
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.
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.
ASKER