[RESOLVED] combo box and excel data type problem

[Resource Dragon]

I have created a combo box that picks up data from a column in an excel spread-sheet. I then want my code to populate various other labels and text boxes in my userform using the value in the combo box.

This is (a cut-down version of) the code that I am using.

Private Sub UserForm_Activate()
For i = 2 To 5
ComboBox1.AddItem Sheets("Sheet3").Cells(i, 1)
Next i
End Sub

Private Sub Frequencies()
For i = 2 To 5
If ComboBox1 = Sheets("Sheet3").Cells(i, 1) Then
TextBox1 = Cells(i, 2)
End If
Next i
End Sub

Private Sub ComboBox1_AfterUpdate()
TextBox1 = ""
Call Frequencies
End Sub

The problem comes in with the fact that there are two types of data in column 1, strings and long numbers. The code works perfectly with strings and ignores the long numbers.

I’ve tried AddItem Cells(i, 1).Value if Cells(i, 1) is numeric, doesn’t work. I’ve tried putting the cell values in a variant array and then adding the array members to the combo-box, also doesn’t work.

I’ve tried using some extremely clunky coding involving the StrConv function but that was creating problems further down the track when the values in the user-form are entered into another sheet.

Does anyone have a simple solution to this problem? Thank you.

[seenu_1st]

long numbers means? can u show any values which is ignored?

[Resource Dragon]

Long numbers are like: 123456 or 89756259 or whatever. My strings look like: 05-E235664 or A908645 or 07-E34608 or whatever.

[kaliman79912]

Is it possible for you to use the ListFillRange and point to your sheet's range instead of adding the items in a loop?

We may need more info since what you posted is working fine.

[kaliman79912]

Now, if the problem is in the Frequencies sub, that it is not finding the numbers with the equal sign is because the combobox only stores strings. Change the logic part to:

If Val(ComboBox1) = Sheets("Sheet3").Cells(i, 1) Then

Also be aware that TextBox1 = Cells(i, 2) is not fully qualified so it depends what sheet is active.

[seenu_1st]

Private Sub UserForm_Activate()
For i = 2 To 5
ComboBox1.AddItem Sheets("Sheet3").Cells(i, 1)
Next i
End Sub
works fine for me.

[Resource Dragon]

The code "If Val(ComboBox1) = Sheets("Sheet3").Cells(i,1) " works for the numbers but not for the strings. If, however, I amend the code to:

Private Sub Frequencies()
For i = 2 To 5
If Val(ComboBox1) = Sheets("Sheet3").Cells(i, 1) Then
TextBox1 = Cells(i, 2)
ElseIf ComboBox1 = Sheets("Sheet3").Cells(i, 1) Then
TextBox1 = Cells(i, 2)
End If
Next i
End Sub

the problem is solved.

So thanks, kaliman79912.