CODEWITHSUNDEEP
excelvba
ASH
ASH

Reputation: 20302

How to display second column in ComboBox after selection?

I can click on my ComboBox and see the values of Column1 and Column2, but after I click on off the ComboBox, the value in Column1 is always displayed and I want the value of Column2 displayed.

enter image description here

enter image description here

I tried this:

        With ComboBox2
            .Value = "None"
            .ColumnHeads = True
            .ColumnCount = 2
            .ColumnWidths = "50;100"
            .RowSource = "SetupQuestions!A42:B48"
            .BoundColumn = 2
        End With

That didn't set the value as I thought it would.

I tried this:

Private Sub ComboBox2_AfterUpdate()
    ComboBox2.Value = ComboBox2.Column(2)
End Sub

That didn't set the value as I thought it would.

How can I force the ComboBox to display the value in Column2 after a selection is made?

Upvotes: 0

Views: 18765

Answers (8)

Mohinder Singh
Mohinder Singh

Reputation: 1

  1. Open Design View
  2. Open Property Sheet
  3. Under the "Data" tab click on the three dots to the right of the "Row Source" item
  4. In the Query Builder that opens, move the desired column you want to view in the combo box field where in the place where the currently viewed column is
  5. Change any VBA code accordingly. For Example: If Column(1) was showing in the combo box field and you moved Column(2) to the place of Column(1), then Column(2) becomes Column(1) and Column(1) becomes Column(2). This will affect any VBA Code referring to Column numbers and also may affect the default value assessed for that Combo Box when a column number is not specified in the VBA code.

Upvotes: 0

Paulo Jorge Gonçalves Pinto
Paulo Jorge Gonçalves Pinto

Reputation: 47

Use a text field ond the right side of the combo box. Set the number of columns in the combo box to 2. Set the list width the size of both combo and text together (a+b) Set the columns width for the size of the combo and the size of the text (a;b) Since the columns property is an 0 based array of columns, set the source of the text field to "=[MyCombo].Columns(1)" to display the second field. When you drop down, the first column should be exactly under the combo box and the second column under the text box. When you update the combo, the text box should update its value accordingly. Additionally you may want to set the text box properties locked to true and enabled to false.

Upvotes: 0

Jedap
Jedap

Reputation: 46

The DropList of a ComboBox can show multiples columns, but after selecting a row, it can show only one column as Text. To show the second column use the property TexColumn.

Me.ComboBox1.TextColumn = 2

Upvotes: 3

Variatus
Variatus

Reputation: 14373

The reason is in your setting of ColumnWidth. Your combobox shows two columns. The second one can't be displayed because the total width of your box is insufficient. Therefore you see the first column only. Set the ColumnWidth to "0;100" and you will see the second column. Make sure that there is a working relationship between the width of the box and that of the columns to be displayed within it.

Upvotes: 0

Murrah
Murrah

Reputation: 1690

An alternative you can use without VBA.

Combo row source (adjust for your situation): 

SELECT Adults.aID, Trim([Adults].[LastName]) & ", " & Trim([Adults].[FirstName]) AS Expr1 
FROM Adults WHERE ((Not (Adults.LastName)=("isNull"))) 
ORDER BY Adults.LastName, Adults.FirstName;

Basically, make your second column a composite single field via SQL.

Bound column: 1, Column count: 2, Column widths: 0cm;4cm

You can use this technique to display whatever you want by building the string representation as a single field.

Upvotes: 0

Jim Poloski
Jim Poloski

Reputation: 1

I just came across here because I was looking to solve this too. but other people's response helped me find the answer. If you haven't gotten it yet, here is my solution.

Private Sub ComboBox_AfterUpdate()

  ComboboxList.Text = ComboboxList.Column(0) & " " & ComboboxList.Column(1)

End Sub

Upvotes: 0

SJR
SJR

Reputation: 23081

If you are only concerned with appearances, there is a workaround

Private Sub ComboBox2_Click()

With ComboBox2
    .Text = .List(.ListIndex, 0) & " | " & .List(.ListIndex, 1)
End With

End Sub

Upvotes: 1

ASH
ASH

Reputation: 20302

Argh! It's not .Value

It's .Text

Private Sub ComboBox2_AfterUpdate()
    Me.ComboBox2.text = Me.ComboBox2.Column(1)
End Sub

Upvotes: 0

Related Questions