ComboBox Only Recognizes Text Items When Selecting a Value To Populate ListBox

[RunTime91]

Greetings All...

Setup:

I am providing the enduser (eu) a list of items in a ComboBox on a UserForm. When the eu selects an item from the ComboBox, a ListBox will populate with another list from which the eu can make a selection to open word/pdf docs providing the eu with written instructions about the topic selected in the ListBox.

Everything works great until the eu selects one of the items in the ComboBox list that is a number instead of text. When this happens the code brings back nothing.
I've tested this and if I change 435 to 435C the ListBox will populate with the links to the documents regarding the 435. If I remove the 'C' the code once again brings back nothing.

I have tried to change the format of the list that populates the ComboBox at the sheet level using the Format Cells window - No change.

So I'm assuming I have to fix this at the code level. Is there an easy fix to this?

Not sure what code I can provide to help you, help me...

But please let me know as, I definitely need help

Sincerely,

RT91

 

[Micron]

Try wrapping the reference in one of the conversion functions. In this case, probably CStr. I use this site when I need a refresher on vba functions.

MS Excel: How to use the CSTR Function (VBA)

This Excel tutorial explains how to use the Excel CSTR function with syntax and examples. The Microsoft Excel CSTR function converts a value to a string.

www.techonthenet.com

 

[RunTime91]

Try wrapping the reference in one of the conversion functions. In this case, probably CStr. I use this site when I need a refresher on vba functions.

MS Excel: How to use the CSTR Function (VBA)

This Excel tutorial explains how to use the Excel CSTR function with syntax and examples. The Microsoft Excel CSTR function converts a value to a string.

www.techonthenet.com

Hey Micron ~ Good to see you are still out there...

Here is the code I'm using as borrowed from another. It uses the CStr on the 'Elm' Variant but it is still not working

UserForm Initialize()

Set RngData = Sheets("I&I").Range("D2:E54")
Me.CmbSelectDocs.List = Array_Unique_Collection(RngData.Columns(1).Value

End Sub

Function Array_Unique_Collection(ByVal NotUniqueArry As Variant) As Variant

Dim cTmp As New Collection
Dim i As Long
Dim aTmp As Variant
Dim vElm As Variant

On Error Resume Next
For Each vElm in NotUniqueArry
     cTmp.Add Cstr(vElm) , CStr(vElm)
Next

On Error GoTo 0
If cTmp.Count = 1 And cTmp.Item(1) = vbNullString Then
Array_Unique_Collection = Null
Exit Function
End If

ReDim aTmp(1 To cTmp.Count)
For i = 1 To cTmp.Count
   aTmp(i) = cTmp.Item(i)
Next
Array_Unique_Collection = aTmp
End Function

 

[RunTime91]

Hello Again ~

I'm still trying to get this to work ~ The ability to be able to make a non-text (numerical) selection in a combobox and have it load a listbox with a list of data that is filtered by the selection.

I have changed my code approach to this and I'm hoping maybe someone can see what mods I can do to this code to achieve this goal

This is what I am now using to populate and sort my combobox with a list of unique items

TtlRows = ThisWorkbook.Sheets("I&I").Cells(Rows,Count, 4).End(xlUp).Row
For i = 2 to TtlRows
Check = True
For k = 0 To CmbSortTest.ListCount -1
  If Cells(i, 4).Value = CmbSortTest.List(k) Then
  Check = False
  Exit For
End if
Next k

If Check Then
  CmbSortTest.AddItem Cells(i, 4).Value
End if

Next i

Dim Tmp as Variant

For i = 0 To CmbSortTest.ListCount - 1
  For j = i To CmbSortTest.ListCount - 1

If CmbSortTest.List(i) > CmbSortTest.List(j) Then
  Tmp = CmbSortTest.List(i)
  CmbSortTest.List(i) = CmbSortTest.List(j)
  CmbSortTest.List(j) = Tmp
End if

Next j
Next i

End Sub

As an added bonus of consternation the above code works perfectly for populating and sorting unique items but only if I am on the Sheet("I&I")

Thank You as always for any guidance

RT 91

 

[Micron]

I meant to try CStr in comparisons like this
If Cells(i, 4) = CStr(CmbSortTest.List(k)) Then
or
If CStr(Cells(i, 4)) = CmbSortTest.List(k) Then
but I'm not sure which. Maybe post some data (XL2BB is good for that) or copy/paste in a post. Pics of data are of little help.

 

[RunTime91]

I meant to try CStr in comparisons like this
If Cells(i, 4) = CStr(CmbSortTest.List(k)) Then
or
If CStr(Cells(i, 4)) = CmbSortTest.List(k) Then
but I'm not sure which. Maybe post some data (XL2BB is good for that) or copy/paste in a post. Pics of data are of little help.

So I finally carved out a few minutes to address this idea and came up with the following

Private Sub CmbSortTest_Change()
Dim RngData As Range
Set RngData = Sheets("I&I")Range("D2:E54")

Dim Foo As Variant

Me.LstHowToDocs.Clear

For i = 2 To RngData.Rows.Count

On Error Resume Next

Foo = RngData.Cells(i, 1).Value

If Foo = CDbl(Foo) Then
  Foo = CStr(Foo)
End If

If Foo = CmbSortTest.Value Then
  Me.LstHowToDocs.AddItem RngData.Cells(i, 2).Value
End If

Next

End Sub

The code word perfectly but I have to believe there is a better way to change a Variant/Double value (which is a numerical value in the Excel sheet)
to a String Value without having to use the On Error Resume Next code.

The Error Code is used because when the cell value is a string the If(Foo) = CDbl(Foo) line throws Type Mismatch Error.

Can anyone help me to clean up this code or is this really the best way to write this?

Thank You,

RT91

 

[Akuini]

Try:

Private Sub CmbSortTest_Change()
Dim RngData As Range
Set RngData = Sheets("I&I").Range("D2:E54")


Me.LstHowToDocs.Clear

For i = 2 To RngData.Rows.Count

If RngData.Cells(i, 1).Text = CmbSortTest.Value Then
  Me.LstHowToDocs.AddItem RngData.Cells(i, 2).Value
End If

Next

End Sub

What is the decimal separator in your system? comma or dot?

 

[RunTime91]

Try:

Private Sub CmbSortTest_Change()
Dim RngData As Range
Set RngData = Sheets("I&I").Range("D2:E54")


Me.LstHowToDocs.Clear

For i = 2 To RngData.Rows.Count

If RngData.Cells(i, 1).Text = CmbSortTest.Value Then
  Me.LstHowToDocs.AddItem RngData.Cells(i, 2).Value
End If

Next

End Sub

What is the decimal separator in your system? comma or dot?

Akuini ~

Works Perfectly!

But now my question is ~ How?

My code loops through an Excel list searching for matches to an item selected in a ComboBox.
All works fine until the loop encounters a number in the Excel list; which of course is stored as a number value.
and doesn't match the same number in the ComboBox List which is, of course stored as a string value.

Thus, my code, when it recognizes this mismatch it changes the format of the number value in Excel to a string value
so it matches the ComboBox Value.

So, how does you code accomplish the same thing?

Thanks Akuini

As for my decimal separator... If I understand your question correctly, mine is a dot.

 

[Akuini]

But now my question is ~ How?

My code loops through an Excel list searching for matches to an item selected in a ComboBox.
All works fine until the loop encounters a number in the Excel list; which of course is stored as a number value.
and doesn't match the same number in the ComboBox List which is, of course stored as a string value.

Actually I don't understand why that happened. If you compare a number with a string usually vba will implicitly convert the string to number, so there should not be a problem. For example:

Sub test()
Dim tx As String
Dim n As Long
tx = "12"
n = 12
Debug.Print tx = n 'returns True
End Sub

In Immediate window you will see that the comparison of string & number(of "12" & 12) returns true.
So, I don't understand the problem you mentioned:

Everything works great until the eu selects one of the items in the ComboBox list that is a number instead of text. When this happens the code brings back nothing.
I've tested this and if I change 435 to 435C the ListBox will populate with the links to the documents regarding the 435. If I remove the 'C' the code once again brings back nothing.

If you can provide an example of your data, I probably can look into it. If you want, you can upload a sample workbook to a file-sharing site like Dropbox or Google Drive and share the link here. Also, ensure that the link is accessible to anyone. If there is sensitive data, please replace it with representative dummy data.

 

[Artik]

RunTime91, try both versions.

With the implicit conversion that Akuini mentioned

Private Sub CmbSortTest_Change()
    Dim v As Variant
    Dim strCmbVal As String
    Dim k As Long
    Dim i As Long
    Dim vLst As Variant

    v = Worksheets("I&I").Range("D2:E54").Value

    strCmbVal = Me.CmbSortTest.Value

    ReDim vLst(1 To UBound(v))

    Me.LstHowToDocs.Clear

    For i = 2 To UBound(v)
        If v(i, 1) = strCmbVal Then
            k = k + 1
            vLst(k) = v(i, 2)
        End If
    Next i

    If k > 0 Then
        ReDim Preserve vLst(1 To k)
        Me.LstHowToDocs.Column = vLst
    End If
End Sub

and with the explicit conversion

Private Sub CmbSortTest_Change()
    Dim v As Variant
    Dim strCmbVal As Variant
    Dim k As Long
    Dim i As Long
    Dim vLst As Variant

    v = Worksheets("I&I").Range("D2:E54").Value

    strCmbVal = Me.CmbSortTest.Value

    ReDim vLst(1 To UBound(v))

    Me.LstHowToDocs.Clear

    For i = 2 To UBound(v)
        If IIf(IsNumeric(v(i, 1)), CDbl(v(i, 1)), v(i, 1)) = strCmbVal Then
            k = k + 1
            vLst(k) = v(i, 2)
        End If
    Next i

    If k > 0 Then
        ReDim Preserve vLst(1 To k)
        Me.LstHowToDocs.Column = vLst
    End If
End Sub

Pay special attention to the types of variables when declaring them.

Artik