Combobox - select first or second value from two column list

[Richard Schollar]

Hopefully an easy one:

I have a ComboBox on my userform that is populated with a two-column list (a numeric field and a string (textual) one). Based on a toggle button, I would like the combobox to return either the numeric field of the selected row or the string field of the selected row.

My problem is I do not know how to specify in code which field (numeric or string) the combobox should pull the value from to return.

Any assistance greatly appreciated!

Richard

 

[Norie]

Richard

Perhaps something like this.

Private Sub ComboBox1_Change()
    Select Case True
        Case ToggleButton1
            MsgBox ComboBox1.Column(0)
        Case Else
            MsgBox ComboBox1.Column(1)
    End Select
End Sub

 

[Richard Schollar]

Norie - thanks for the reply and I can see if I copy & paste your code into the userform code module that it returns the applicable field value just as I requested when I select a particular row from my combobox.

However, I do not seem to be able to set what value is then displayed in the combobox after selection (eg if I toggle for the textual field (which is the second column)) and then select a row from my combobox, your code makes a msgbox appear with the text "Gross Premiums" but the value displayed by the combobox is still its numeric equivalent "1001".

Have I managed to explain myself here, and can you suggest a solution?

Thanks

Richard

 

[Andrew Poulsom]

Try:

Private Sub UserForm_Initialize()
    ToggleButton1.Value = False
End Sub

Private Sub ToggleButton1_Click()
    If ToggleButton1.Value = False Then
        ComboBox1.TextColumn = 1
    Else
        ComboBox1.TextColumn = 2
    End If
End Sub

 

[Richard Schollar]

That did it Andrew!

Many thanks!

Richard

 

[Norie]

So you wanted to change what was displayed in the combobox?

 

[Richard Schollar]

Yes - Norie, sorry if I failed to make that clear originally

Richard