Solved

Userform combobox autofill

Posted on 2013-10-23
6
1,210 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Back Up Your Microsoft Windows Server®

Back up 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

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
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…

828 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