Showing correct value in MSForms.Combobox

Topic

New Reply

I have a problem with a MSForms combobox which I have created on a userform. The form contains details of an object, and the combobox is populated with a list from a database, and has 2 columns – the first contains the ID (this is the BoundColumn, and hidden when the user views it), the second the description, as follows :

ID Description
— ————-
6 Gas Asia
3 Global Oil Americas
2 Global Oil EH
1 Global Oil Europe
5 GTEL
4 NAGP
7 Not Known

I create a new record, and save it with an ID of 1 (Global Oil Europe).
The problem I have is that when I want to load the saved object record in the form, I cannot set the ComboBox to show the correct value. I cannot use the Value setting, because the ID column is hidden, and I cannot use ListIndex either as setting it to 1 gives me Global Oil Americas.

There must be a way round this, but I cannot work out what it is.

Any help gratefully received

Bodders

Reply | Quote

Replies

You could use code like this:

Code:

  Dim intValue As Integer
  Dim i As Integer
  intValue = 1 ' the ID of the record to select
  For i = 0 To Me.ComboBox1.ListCount - 1
	If Me.ComboBox1.List(i) = intValue Then
	  Me.ComboBox1.ListIndex = i
	  Exit For
	End If
  Next i

where ComboBox1 is the name of the combo box.

Reply | Quote

You could use code like this:

Code:

  Dim intValue As Integer
  Dim i As Integer
  intValue = 1 ' the ID of the record to select
  For i = 0 To Me.ComboBox1.ListCount - 1
	If Me.ComboBox1.List(i) = intValue Then
	  Me.ComboBox1.ListIndex = i
	  Exit For
	End If
  Next i

where ComboBox1 is the name of the combo box.

Hans

Thanks very much for that. I was hoping there was just a property like Itemdata in an Access form to set, but it looks like I will have to use this loop.

Bodders

Reply | Quote

You have to set the ListIndex to the appropriate value.
You could declare a variable at the top of the userform module:

Private arrIndex()

Populate it in the UserForm_Initialize event, after populating the combo box:

Code:

  Dim i As Integer
  Dim n As Integer
  n = Me.ComboBox1.ListCount
  ReDim arrIndex(1 To n)
  For i = 0 To n - 1
	arrIndex(Me.ComboBox1.List(i)) = i
  Next i

You can then use a single line to select the item with ID = 1:

Me.ComboBox1.ListIndex = arrIndex(1)

So you still need a loop, but you don’t have to repeat it each time you want to set the value of the combo box.

Reply | Quote