Combobox select index

[philipad]

Hi all,

The code below opens an Excel file and it fills a Combobox with values from a column. To do that I use a for-to-next function with an index "i".
I need the program to return the corresponding value of the index, for the item which will be selected in the combobox, in order to use it for lookups in the Excel.

Thanks

Dim i As Integer

Private Sub Form_Load()

Dim xlApp As New Excel.Application
Set xlWB1 = Excel.Application
xlApp.Visible = True
xlApp.Workbooks.Open "c:\book1.xls", , , True

For i = 1 To 10
Combo1.AddItem Cells(i, 1)
Next

End Sub

 

[Andrzejek]

If you do:

Combo1.Clear
For i = 1 To 10
    Combo1.AddItem Cells(i, 1)
Next

Your first Item will have an index of 0, Second index of 1, third index of 2, and so on...

Unless you want something like this:

Combo1.Clear
For i = 1 To 10
    Combo1.AddItem Cells(i, 1) & " with index of " & i
Next

Have fun.

---- Andy

 

[philipad]

ok....so?

 

[Andrzejek]

So? Don't get cocky, I am trying to help you here.

Also, I am trying to get what you are trying to do.

If you give me what you have, and what you are hoping to get, maybe we will get somewhere.....?

Have fun.

---- Andy

 

[vb5prgrmr]

Option Explicit

Private Sub Combo1_Click()
Print Combo1.ListIndex
End Sub

Private Sub Form_Load()
Dim NCnt1 As Integer
For NCnt1 = 0 To 10
  Combo1.AddItem (NCnt1 * 100)
Next NCnt1
End Sub

CODE
Combo1.Clear
For i = 1 To 10
Combo1.AddItem Cells(i, 1) & " with index of " & i
Next

should be

Combo1.Clear
For i = 1 To 10
    Combo1.AddItem Cells(i, 1), i 'with index of...
Next

Good Luck

 

[Andrzejek]

I was just trying to figure out what philipad is after. Can not read his/het mind... (yet...)

Your code, vb5prgrmr, will crash:
[tt]
Combo1.Clear
For i = 1 To 10
Combo1.AddItem Cells(i, 1), i 'with index of...
Next[/tt]

first Item you try to add at position 1, and since it is a 0 based - you don't have this position yet, you just have position 0. You get "error 5, Invalid procedure call or argument"

Have fun.

---- Andy

 

[vb5prgrmr]

so then i-1, (off top of head) however the code above with no index does work and so would be print Combo1.ListItem + 1

 

[philipad]

the value of index "i" must be returned after the user will select an item from the combobox.
Let's say i need the index of the item in a textbox or a label

Thanks all for your replies

 

[SBerthold]

philipad, to save the index "i", and not the combo's index, use the .ItemData property:

Public Sub testCombo()
    Dim i As Integer
    [COLOR=green]
    '=================================================
     [/color]
    Combo1.Clear[COLOR=green]
    '=================================================
  
    'Add some items[/color]
    For i = 1 To 10
        Combo1.AddItem CStr(Chr(64 + i))[COLOR=green]
        '(CStr(Chr(64 + i)) just supplies some letters as test values[/color]
        [COLOR=green]
        'Store the index (not the combo's index[/color]
        Combo1.ItemData(Combo1.NewIndex) = i
    Next
    [COLOR=green]
    '=================================================
    'Simulate user selecting the second item:[/color]
    Combo1.ListIndex = 1
    [COLOR=green]
    '=================================================

    'See if and item was selected by user[/color]
    If Combo1.ListIndex >= 0 Then
        [COLOR=green]'Get the stored index (not the combo's index)[/color]
        i = Combo1.ItemData(Combo1.ListIndex)
    End If[COLOR=green]
    '=================================================

    'Print the results[/color]
    Debug.Print i
End Sub