VBA Combobox - get value of Column 2 of selected

I have a combobox with 2 columns. This gives me the value of the Column 1 of the selected item.
MsgBox Me.cmbSpecificationType.Column.Value

Open in new window


I need to get the value of the 2nd column.

I tried ...
MsgBox Me.cmbSpecificationType.Column(1).Value

Open in new window


And I get 'invalid property array index' error.

What I am doing wrong?
Thanks
hindersalivaAsked:
Who is Participating?
 
Martin LissOlder than dirtCommented:
With "M", etc in column 2 then this works in the Click event.
Private Sub cmbSpecificationType_Click()
If Me.cmbSpecificationType.Column(1) = "M" Or _
    Me.cmbSpecificationType.Column(1) = "E" Or _
    Me.cmbSpecificationType.Column(1) = "C" Then
    MsgBox "Found"
End If
End Sub

Open in new window

0
 
Martin LissOlder than dirtCommented:
With cmbSpecificationType
    .AddItem "Column 1"
    .Column(1, 0) = "Column 2"
End With

Open in new window

0
 
hindersalivaAuthor Commented:
Martin, the ComboBox is already populated.  I'm trying to read the Column 2 value of the selected item.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Martin LissOlder than dirtCommented:
Oh, sorry. The default property for the combobox is Text so all you need is
MsgBox cmbSpecificationType.Column(1)

Open in new window

0
 
hindersalivaAuthor Commented:
Martin, looks like it's something else I'm doing wrong that's stupid. That code works ok outside the _Change sub.
This is what I have.

This works
Private Sub cmbSpecificationType_Change()
    
    If Me.cmbSpecificationType.Value = "Mechanical and Public Health Services" Or _
                Me.cmbSpecificationType.Value = "Electrical Services" Or _
                Me.cmbSpecificationType.Value = "Building Management Systems" Then

Open in new window


But this does not
Private Sub cmbSpecificationType_Change()

    If Me.cmbSpecificationType.Column(1) = "M" Or _
                Me.cmbSpecificationType.Column(1) = "E" Or _
                Me.cmbSpecificationType.Column(1) = "C" Then

Open in new window

M, E and C are in Column 2 of the ComboBox.

Can you see what I'm doing wrong?
Thanks
0
 
Martin LissOlder than dirtCommented:
What is actually in the 2nd column; "Mechanical and Public Health Services", or "M"?
0
 
hindersalivaAuthor Commented:
Ah yes. Martin, it works with _Click. So I'll go with _Click.
But, isn't the right way to respond to a ComboBox selection _Change? Curious.
0
 
Martin LissOlder than dirtCommented:
Since I've answered your original question, please do me a favor and close this question and then ask a new one about the Change event.
0
 
hindersalivaAuthor Commented:
Ok.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.