CODEWITHSUNDEEP
stringvbalistbox
Matt
Matt

Reputation: 179

VBA Excel Strings

While using the VBA for Excel, I am facing runtime error with the string datatype.

I have a variable declared as string and trying to read a value from the cell in the spreadsheet and add as an item in a listbox. The data that is present in the particular cell is greater than 2000 character. My vba is able to read the value from the cell and print in the debug window. When I try to add the item in the listbox, it is not able to add. It is throwing an runtime error.

The runtime error code is "-2147352571 (80020005): Type Mismatch".

Is there any work around for this sort of problem. 

Public Sub update_form()
Dim a1, b1, c1, d1 As Single
Dim a2, b2, c2, d2 As String
Dim a3, b3, c3, d3 As String
Dim a4, c4, d4 As String
Dim i As Single
Dim b4$

    a2 = req_no.Value
    Sheets("Design Trace - Current").Select
        Range("A1").Activate
        Columns("A:A").Select
        Selection.Find(What:=a2, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
        a1 = ActiveCell.Row
    b2 = Sheets("Design Trace - Current").Cells(a1, 2).Value

    c2 = "A" & a1
    d2 = "BBB" & a1
    b1 = Application.WorksheetFunction.CountA(Range(c2, d2))
    For i = 2 To b1 Step 3
        a4 = Cells(a1, i).Value
        b4 = Cells(a1, i + 1).Value
        d1 = Len(b4)
        Debug.Print " Length : " & d1
        c4 = Cells(a1, i + 2).Value

        design_ele.Text = a4
        reverse_req.Text = b4
        code_file_name.Text = c4
        Debug.Print "a4 : " & a4
        Debug.Print "b4 : " & b4
        Debug.Print "c4 : " & c4

        If (Len(a4) > 500) Then
            ListBox1.AddItem "Refer Value"
        Else
            ListBox1.AddItem a4
        End If

        ListBox2.AddItem b4

        If (Len(c4) > 500) Then
            ListBox3.AddItem "Refer Value"
        Else
            ListBox3.AddItem c4
        End If


    Next
End Sub

It is an userform and the version of excel is 2007.

Thanks

Upvotes: 0

Views: 1074

Answers (1)

Alex K.
Alex K.

Reputation: 175986

There is a ~2000 character limit to an individual listbox item.

If you want to avoid the error, limit the length when adding:

listbox3.additem left$(a4, 2000)

If you want to store the full text do so in an array.

Upvotes: 1

Related Questions