Solved

Userform combobox autofill

Posted on 2013-10-23
6
1,146 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 81

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 81

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 81

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

914 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

15 Experts available now in Live!

Get 1:1 Help Now