Solved

Userform combobox autofill

Posted on 2013-10-23
6
1,128 Views
Last Modified: 2013-10-24
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
0
Comment
Question by:HEMIChallenger
  • 3
  • 3
6 Comments
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 39596032
To populate the combobox when the userform loads, put the following code in the userform code pane:
Private Sub UserForm_Initialize()
Dim cel As Range, rg As Range
With ActiveSheet
    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

DATA-Q28275571.xlsm
0
 
LVL 80

Expert Comment

by:byundt
ID: 39596044
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

0
 

Author Comment

by:HEMIChallenger
ID: 39597955
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?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 80

Expert Comment

by:byundt
ID: 39598191
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.
0
 

Author Comment

by:HEMIChallenger
ID: 39598297
Thank you Sir.
0
 

Author Closing Comment

by:HEMIChallenger
ID: 39598302
Work great. Thank you.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

My experience with Windows 10 over a one year period and suggestions for smooth operation
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now