Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Userform combobox autofill

Posted on 2013-10-23
6
Medium Priority
?
1,433 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 2000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
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.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

885 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