Click here to Skip to main content
16,018,158 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I have built a data table and a data search function for Excel using a VBA ListBox. Everything works perfectly, but now I want to add a title to the search results. Does anyone know how to do this?

Currently, the title is not included in the search results when the program runs.

What I have tried:

Here is my entire VBA code. I have very limited knowledge of Excel and VBA, so I would greatly appreciate your help.

VB
Private Sub input_search_Change()
    Dim arr(), result, i As Long, a As Long, dk As String
    dk = input_search.Text
    
    arr = Sheets("Sheet1").Range("A4:D20006").Value
    ReDim result(1 To UBound(arr, 1), 1 To 6)
    
    For i = 1 To UBound(arr, 1)
        If arr(i, 2) Like "*" & dk & "*" Or _
         arr(i, 4) Like "*" & dk & "*" Then
        a = a + 1
        result(a, 1) = arr(i, 1)
        result(a, 2) = arr(i, 2)
        result(a, 3) = arr(i, 3)
        result(a, 4) = arr(i, 4)
        End If
    Next i
    
    lstwebsite = ""
    lstwebsite.Clear
    lstwebsite.List = result

End Sub
Private Sub UserForm_Initialize()
lstwebsite.List = Sheets("Sheet1").Range("A4:D20006").Value
End Sub
Posted

You have several options

1. Write your headers to a Worksheet Range followed by your results. Define the RowSource for the ListBox as that range ensuring property ColumnHeads = True

If you don't want to write the results out to a Worksheet then ensure ColumnHeads = False and try one of the following

2. Place Labels above the Listbox with the headers you want. Carefully line them up with the columns. Yes this is a PITA. Or you can loop through the columns of the listbox and try to set the width of the labels to a function of the width of each column

3. Place a second ListBox above your results list box. ColumnHeads = False. Add a single item which is the text of your headers (you can do this in code). Loop through all the columns setting the widths to be the same as your results listbox.
 
Share this answer
 
The best help we can offer is telling you to use Excel macro recorder. You will get VBA code that you will can copy and paste in your final macro.

But, you should look toward existing Excel functions. There are some tailored for Database searching. Excel intrinsect functions are faster than VBA.

Also explore Data ribbon in Excel...
 
Share this answer
 
Comments
duytoi 26-Jul-24 0:19am    
Thank you for your advice, the issue has been resolved
CHill60 26-Jul-24 3:15am    
Reason for my downvote - The OP is trying to put headers onto a VBA ListBox so there is nothing to record. Databases were not mentioned either.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900