CODEWITHSUNDEEP
excelvbaidentifier
Gareth
Gareth

Reputation: 5243

Excel VBA Pass Id of Value in Combobox

Looking for advice on the best way to pass an Id of a value into a parameter in excel VBA.

Essentially I'm trying to replicate getting the value rather than the text itself like for example in html:

<option value="1">Option one</option>

Would return 1. I could concatenate the Id to the start or end of the string with something like:

.additem varList(0, 1) & " | " & varList(1, 1)

But I'm looking for a 'cleaner' option if that makes sense?

Cheers

Upvotes: 2

Views: 6640

Answers (1)

David Zemens
David Zemens

Reputation: 53623

Create your combobox with at least 2 columns. This can be set using the ColumnCount property, via the VBE or through VBA code.

enter image description here

You can then adjust the ColumnWidths property to make one of the columns a width of 0 so it will not be displayed/visible to users.

WHen you populate the combobox, simply put the ID in one column of the ComboBox, and put the value in the other visible column. The interface will look like this, unless you adjust the columnwidths

enter image description here

Use the BoundColumn of the ComboBox to return the appropriate value, or you can do some iteration over the selected item(s) and refer to the indexed position:

Debug.Print Me.ComboBox.List(0, 0)  '# Display the first row item in the first column

Debug.Print Me.ComboBox1.List(0, 1)  '# Display the first row item in the SECOND column

Upvotes: 6

Related Questions