Link to home
Start Free TrialLog in
Avatar of HEMIChallenger
HEMIChallenger

asked on

Userform combobox autofill

Hello all,

Could a userform combobox autofill with data added in cell range $D16:$D? I attached a form, what I like is, as the form gets filled with names that the combobox will autofill using the names that have been entered. The sheet name will also be changing from range("F2").value.
DATA.xlsm
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America 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
I wasn't quite sure how you intended to use cell F2.

If you meant to click a button on worksheet User1, but get data from User2 (the selection in User1 cell F2), then the code might be:
Private Sub UserForm_Initialize()
Dim cel As Range, rg As Range
Dim ws As Worksheet
Set ws = ActiveSheet
If ws.Range("F2") <> "" Then Set ws = Worksheets(ws.Range("F2"))
With ws
    Set rg = .Range("D16")
    Set rg = Range(rg, .Cells(.Rows.Count, rg.Column).End(xlUp))
    If rg.Row < 16 Then Exit Sub
End With
For Each cel In rg.Cells
    If cel.Value <> "" Then
        NameBox1.AddItem cel.Value
    End If
Next
End Sub

Open in new window

Avatar of HEMIChallenger
HEMIChallenger

ASKER

Nice the first code works perfect. Another question the textbox1 (Partbox1) when i enter field the tab it goes to the next textbox2 but select tab again it skips the combobox and goes to the textbox3. Also with the down arrow once i get to the combobox press down arrow it doesnt go to the next field  textbox 3. Is there a way to fix this?
You fix the tab order by setting the TabIndex property of each of the controls on your userform:
1. In the VBA Editor, select the userform
2. Make sure that the Properties pane is displayed. Use View...Properties Window if it is not.
3. Select the item that you want in the tab order. Presumably, this is PartBox1.
4. Note the TabIndex property for that item. The first item will be TabIndex 0, the second will be TabIndex 1, etc.
5. If you look at NameBox1, it has TabIndex 9, which is out of sequence with the rest. Change that value to 2 and the sequence will be PartBox1, BuildingBox2, NameBox1, EmployeeBox4, CommandButton1, CommandButton2.

I don't know how to make the arrow keys not get "stuck" when hitting NameBox1. Sorry.
Thank you Sir.
Work great. Thank you.