How to add a head to the search results in excel using VBA

Question

1.00/5 (2 votes)

See more:

Excel-2016

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 24-Jul-24 6:51am

duytoi

Add a Solution

2 solutions

Solution 1

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.

Posted 24-Jul-24 23:40pm

CHill60

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

cmarcotte · Accepted Answer · 2024-07-25T15:03:00

Solution 3

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...

Posted 25-Jul-24 15:03pm

cmarcotte

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.