userform combobox populated from two columns of a table

zoog25 · Apr 26th 2017

Hello all,

Here is my situation:

I have a userform called ufExisting. On the userform i have a combobox called cmbExisting.

Now here is what i'm trying to do.

On a sheet called "Client Records" i turned it into a table called [ClientRec].

Now i want to populate the combobox with columns tables 1 and 6.

The reason i'm doing this is cause the combobox selection will open a sheet in the workbook that is based on the information from the column 1 table.

Thank you for any assistance that you can provide.

adygelber · Apr 26th 2017

Re: userform combobox populated from two columns of a table

Hey,

Try this out (just replace Col1 and Col2 with the actual column names in the first two lines):

Code

Set rng1 = Sheets("Client Records").Range("ClientRec[Col1]")
     Set rng2 = Sheets("Client Records").Range("ClientRec[Col6]")
    
     For Each cl In rng1
         If arStr = "" Then
             arStr = cl.Value
         Else
             arStr = arStr & "," & cl.Value
         End If
     Next cl
     
     For Each cl In rng2
         If arStr = "" Then
             arStr = cl.Value
         Else
             arStr = arStr & "," & cl.Value
         End If
     Next cl
     
     ufExisting.cmbExisting.List = Split(arStr, ",")

Display More

Cheers,
Adrian B.

zoog25 · Apr 26th 2017

Re: userform combobox populated from two columns of a table

Hello Adrian

I tried your code but I get an error message at set rng1. I don't know why it doesn't recognize it.

adygelber · Apr 26th 2017

Re: userform combobox populated from two columns of a table

Did you replaced Col1 with your column name?

**norie** · Apr 26th 2017

Re: userform combobox populated from two columns of a table

How many columns in your combobox?

**royUK** · Apr 26th 2017

Re: userform combobox populated from two columns of a table

I would load all the data into the combobox.

Set the number of columns to however many you want then hide the other columns using the columnwidth property.

Code

Option Explicit


Dim oTbl As ListObject


Private Sub UserForm_Initialize()
    Set oTbl = Sheet1.ListObjects(1)
''//this code loads the whole of the Table into the ComboBox
''/// the column width for the first column is set to zero so only two columns are visible
''///amend the e.g "50,0,50" will hide column 2


    With Me.ComboBox1
        .ColumnCount = 3
        .ColumnWidths = "0,50,50"
        .List = oTbl.DataBodyRange.Value
    End With
End Sub

Display More

zoog25 · Apr 26th 2017

Re: userform combobox populated from two columns of a table

I'm doing two columns in the combobox which would look like one.

**norie** · Apr 26th 2017

Re: userform combobox populated from two columns of a table

So you want to concatenate the data from columns 1 and 6 from the table and have the result appear in the combobox list?

What data is in those 2 columns and how will you use the first column to determine which worksheet to 'open'?

Could you upload a sample workbook?

zoog25 · Apr 26th 2017

Re: userform combobox populated from two columns of a table

Column 1 has a project code like "077-09", Column 6 has the project name. Now each sheet in the workbook is title by the job number so in this case "077-09". So it takes the number sequence of ###-## and then opens that worksheet.

Now I tried Adrian code and it works but instead of shows it like "077-09 Project Name" it goes

077-09
Project Name

so now I just need to have first the columns to combine in the list so it appears at 077-09 Project Name and then once selected the userform has a button that would essentially open the worksheet (Show, Unhide, Select, how ever that is called) and the user would see the project sheet.

**royUK** · Apr 27th 2017

Re: userform combobox populated from two columns of a table

So is the sheet simply named after the column one contents?

If so use my code and

Code

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Sheets(Me.ComboBox1.Value).Activate
End Sub

If the sheet name is a combination of columns 1 and 6 then use

Code

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me.ComboBox1
Sheets(.Value & .List(.ListIndex, 5)).Activate
End With
End Sub

adygelber · Apr 27th 2017

Re: userform combobox populated from two columns of a table

Hello again,

I clearly misunderstood you request.

Please paste the above code in your userform code area:

Code

Private Sub CommandButton1_Click()


    If cmbExisting.Value <> "" Then Sheets(cmbExisting.Value).Select
    
End Sub


Private Sub UserForm_Initialize()


    Set myTbl = Sheets("Client Records").ListObjects("ClientRec")
    
    lr = myTbl.Range.Rows.Count
    
    For i = 2 To lr
        If arStr = "" Then
            arStr = Cells(i, Range("ClientRec[Col1]").Column) & " " & Cells(i, Range("ClientRec[Col6]").Column)
        Else
            arStr = arStr & "," & Cells(i, Range("ClientRec[Col1]").Column) & " " & Cells(i, Range("ClientRec[Col6]").Column)
        End If
    
    Next i


     
    ufExisting.cmbExisting.List = Split(arStr, ",")


End Sub

Display More

Here is the sample workbook:
forum.ozgrid.com/index.php?attachment/72281/

Cheers,
Adrian B.

zoog25 · Apr 27th 2017

Re: userform combobox populated from two columns of a table

Thanks royUK for the information. I will test your code once I get the first one from Adrian B's code.

Now the code for the combo box works except instead of show two columns, all I get is one column with first information and then second information below it. I'm attaching a sample of the workbook to make things easier. Please see what you guys can do to fix it.

zoog25 · Apr 27th 2017

Re: userform combobox populated from two columns of a table

Hey all thanks for the help.

I was able to make a few adjustments and got the code userform to work how I wanted it too. This saved me a lot for the ultimate project I'm working on. I'm including the final code below:

Code

Private Sub cmdbtOpen_Click()
Dim PSH As String
If cbExisting.Value <> "" Then
    PSH = Left(cbExisting.Value, 6)
    Sheets(PSH).Show
    Sheets(PSH).Select
End If
End Sub
Private Sub UserForm_Initialize()
     
    Set myTbl = Sheets("Client Records").ListObjects("ClientRec")
     
    LR = myTbl.Range.Rows.Count
     
    For i = 2 To LR
        If arStr = "" Then
            arStr = Sheets("Client Records").Cells(i, Range("ClientRec[Project ID]").Column) & " " & Sheets("Client Records").Cells(i, Range("ClientRec[Project Name]").Column)
        Else
            arStr = arStr & "/" & Sheets("Client Records").Cells(i, Range("ClientRec[Project ID]").Column) & " " & Sheets("Client Records").Cells(i, Range("ClientRec[Project Name]").Column)
        End If
         
    Next i
    
    ufExisting.cbExisting.List = Split(arStr, "/")
     
End Sub

Display More

userform combobox populated from two columns of a table

Participate now!

Share

Users Viewing This Thread