Please help with my ListBox

[jejeserafico]

This is my code:


Worksheets(sheetName).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
findData = InputBox("Type the keyword to search" & vbCrLf & "(Search only by Ticket no, Date or LDAP)", "Search Engine")

If findData = "" Then
MsgBox "Please Fill Up The Fields!", vbCritical

ElseIf WorksheetFunction.CountIf(Workbooks(fileName).Worksheets(sheetName).Range("D2", Workbooks(fileName). Worksheets(sheetName).Cells(iRow, 1)), findData) = 0 Then
MsgBox "Not Found!", vbCritical

Else
listbox1.clear
'I want to find a specific value from spreadsheet to listbox
'when i type the ID in findData, the listbox must be populated with only one line or multiple lines depending on the inputdata
End If


For Example:

11301

[Bob Phillips]

That red on grey background is not really legible.

[jejeserafico]

ooops sorry
first line - This is the listbox populated by data
second line - This is where i put my data to search
third line - If i put "2/14/2014" in inputbox, i want only all the info with this date populate my listbox
or by assignee or ticketid

[Jomathr]

you'll have to do something similar to this:



Dim x As Integer
Dim c As Range

Listbox.Clear 'to remove previous search
x = Me.Listbox.ListCount 'this is in case you don'T want to remove previous searches, it will append the new data at the end of the list

For Each c In Worksheets("Sheetname").Range(ExcelTable).Cells
If c.Value = findData Then
With Listbox
.AddItem
.List(x, 0) = c.Value
.List(x, 1) = c.Offset(0, 1).Value
.List(x, 2) = c.Offset(0, 2).Value
'.List(X, 2) = C.Offset(0, 3).Value
.List(x, 3) = c.Offset(0, 4).Value
.List(x, 4) = c.Offset(0, 5).Value
.List(x, 5) = c.Offset(0, 6).Value
'.List(X, 5) = c.Offset(0, 7).Value
.List(x, 6) = c.Offset(0, 8).Value
.List(x, 7) = c.Offset(0, 9).Value
.List(x, 8) = c.Offset(0, 10).Value
.List(x, 9) = c.Offset(0, 11).Value
x = x + 1
End With


You will have to tweak this a bit so you can look for different data but that's the main logic I use when I have to do something like that.

there are other way of doing this but this is probably the simplest one to understand the logic in my opinion.

one thing to remember when working with listbox, you can only use 10 columns

hope this helps

[Bob Phillips]

Hi

Pls Give Me Solution For Set Expire Date For excel vba.

Current Date get from Internet server and set file expire date.

Either move this post to its own thread and give more details on what you want to do by the end of today. or I will delete it and ban you.

[Bob Phillips]

ooops sorry
first line - This is the listbox populated by data
second line - This is where i put my data to search
third line - If i put "2/14/2014" in inputbox, i want only all the info with this date populate my listbox
or by assignee or ticketid

You are going to have to build a new list based upon the filtered data, and then load the listbox from that. Not a trivial task, but doable.

Without the workbook I cannot offer more help, I am not going to recreate your data to come up with a solution.

[jejeserafico]

thank you so much, it really helped me a lot
i just tweaked it a little bit and it worked! :clap:

by the way, is there any way to make the 1st row my header for listbox?

[jejeserafico]

you'll have to do something similar to this:



Dim x As Integer
Dim c As Range

Listbox.Clear 'to remove previous search
x = Me.Listbox.ListCount 'this is in case you don'T want to remove previous searches, it will append the new data at the end of the list

For Each c In Worksheets("Sheetname").Range(ExcelTable).Cells
If c.Value = findData Then
With Listbox
.AddItem
.List(x, 0) = c.Value
.List(x, 1) = c.Offset(0, 1).Value
.List(x, 2) = c.Offset(0, 2).Value
'.List(X, 2) = C.Offset(0, 3).Value
.List(x, 3) = c.Offset(0, 4).Value
.List(x, 4) = c.Offset(0, 5).Value
.List(x, 5) = c.Offset(0, 6).Value
'.List(X, 5) = c.Offset(0, 7).Value
.List(x, 6) = c.Offset(0, 8).Value
.List(x, 7) = c.Offset(0, 9).Value
.List(x, 8) = c.Offset(0, 10).Value
.List(x, 9) = c.Offset(0, 11).Value
x = x + 1
End With


You will have to tweak this a bit so you can look for different data but that's the main logic I use when I have to do something like that.

there are other way of doing this but this is probably the simplest one to understand the logic in my opinion.

one thing to remember when working with listbox, you can only use 10 columns

hope this helps


thank you so much, it really helped me a lot
i just tweaked it a little bit and it worked! :clap:

by the way, is there any way to make the 1st row my header for listbox?

[Jomathr]

In your case it's not possible since the only way to have headers from a Excel sheet is to use the RowSource property wich require a contiguous table.

Either you change the code to build a table with the result of your search and then use the row source property of listbox or you have to use labels as headers (wich can be dynamically set if you use the label.caption = range()). it's a pain to set up the first time to have it match your listbox column width but are no real other way to do this without using RowSource.

[Bob Phillips]

I would just put a label above the listbox with column names.

[jejeserafico]

Thanks to both of you masters:bow:, i just put a label on top for the headers