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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.