• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Userform Listbox query

Svmaxcel

Member
Team,

Based on earlier suggestion, I have prepared the below code.
I created a user form, image attached.

Let me give you an insight into how it works for easy understanding.

As you see the image, its optional for the user to select the dates.
User has 2 options Select from List and Instant Search.

When the user selects Instant Search, text box is focused and user can type the initials of the person, he is looking for and the list box displays 2 rows of Manager Name and Emp Name.

Problem Area
Only 2 Columns are displayed, I want 5 columns with the data from the sheet.
I can change the column count in listbox propertly, but how do i get more data in list box columns.

The Raw Data i have has Columns starting from A to AB, and i need the data in List box from col I to M.
Below is the code, and this works fine for 2 columns

Cannot attach File due to security reason



Code:
Private Sub InstantEmp_Change()
Dim i As Long
Me.InstantEmp.Text = StrConv(Me.InstantEmp.Text, vbProperCase)
Me.DataResult.Clear
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("I:I"))
a = Len(Me.InstantEmp.Text)
If Left(Sheet1.Cells(i, 9).Value, a) = Left(Me.InstantEmp.Text, a) Then
Me.DataResult.AddItem Sheet1.Cells(i, 10).Value
Me.DataResult.List(DataResult.ListCount - 1, 1) = Sheet1.Cells(i, 9).Value
End If
Next i
End Sub
 

Attachments

charlesdh

Member
xvmaxcel,
I think you need to check the "List" box column count. If it's 2 then change it to 5. This may work.
 

charlesdh

Member
Thanks,
I just sat down to work on this file and was about to create my own file.
But, received mail that you were going to send a file.
 

Svmaxcel

Member
Dear Team,

I have uploaded the file.

User can select only 1 option, Either instant search or Select from list

What i want is --

1)when i click on Instant search and type employee name, i want data matching to be displayed from Data Sheet Column E to P in DataResult

2a)when i click on Select from list and fille in LOB Name in Combo box, i want relevant data to come in DataResult (Data Sheet Column E to P)

2b)Similarly when a user selects LOB Name, Manager Name then relevant info should be displayed in DataResult (Data Sheet Column E to P)

2c)Similar goes for Lob Name/Manager Name and Emp Name.

I hope i am trying to make my query understandable to all of you, the coding might be too complex here, but please ask any relevant questions if any
 

Attachments

charlesdh

Member
Svmaxcel,
Are you wanting the "Listbox" to list the required range first and then refresh when the user make their selection and only show only their selection?
 

charlesdh

Member
Hi,
I have a code worked out for the desired selection of the "Instant" search.
It will populate the listbox. But, at this time I need to modify it so that it will repopulate the list box as you type the desired name to show.
I will be gone reset of today and tomorrow.
 

Svmaxcel

Member
Hi,
I have a code worked out for the desired selection of the "Instant" search.
It will populate the listbox. But, at this time I need to modify it so that it will repopulate the list box as you type the desired name to show.
I will be gone reset of today and tomorrow.
Thanks for your help

Let me give you an example

User clicks on Instant Search and types ''s', at this time I want all employees name starting with S.

Now if the user wants to get employees name starting with 'd', he will clear the text box and type d. At this time listbox should refresh and only show employees names starting with D.
 

Svmaxcel

Member
A
Hi,
I have a code worked out for the desired selection of the "Instant" search.
It will populate the listbox. But, at this time I need to modify it so that it will repopulate the list box as you type the desired name to show.
I will be gone reset of today and tomorrow.
Hope you are back, please assist.
 

charlesdh

Member
Hi,
I should have mentioned that to tell I re named your userform to "frmDastForm" and changed the list box name to "lstDataResult". You should refrain from using "Me". For list box try to use "lst" for command button try to use something like "cmd". The same pattern used for other controls.
 

Svmaxcel

Member
Hi,
Here's a file you check. I only coded for "Instant Search".
To ope
Thanks, You did an excellent job and sorted the issue, however, there are some more queries.

1)The List box displays the results well, however, the Top Row is blank, also I need data from column E to P in list box.

2)When I type the Initials of the name, List Box only displays The name and all other columns are blank, however, when I click on Lets Go, it displays the full information. I mean when I type any initials the entire row should be visible rather than the name when I click on Lets Go again, I get an error.

3)Sliders are not available to scroll the list (up-down), (right-left)

4)Filters
4a
)I want the same search to happen in Combobox also, so if a user select LOB, the listbox should filter and display all data of LOB
4b)if user select LOB&Manager, then listbox should display data based on the results
4c)Same goes for .....EMP Name...
4d)Can we filter the data in list box based on Start Date/End Date.

6)Can we rather skip the Lets Search button and let the text box change the listbox on(textbox_change), it would save the extra clicks.
 

charlesdh

Member
Svmaxcel,

I'll check on the slider. When I ran the code "My code" it seems that the slider did go from left to right. The listbox only shows selection for the desired employee. That may be the reason that you do not have top to bottom.
As for the "Header" when starting you search I'll check on that and also on the full line of data to be shown.
I'll look you step 4 and see what I can do.
 

Svmaxcel

Member
Svmaxcel,

I'll check on the slider. When I ran the code "My code" it seems that the slider did go from left to right. The listbox only shows selection for the desired employee. That may be the reason that you do not have top to bottom.
As for the "Header" when starting you search I'll check on that and also on the full line of data to be shown.
I'll look you step 4 and see what I can do.
Appreciate you helping me.
Thanks a ton again
Please watch the video for error message
HTML:
https://vimeo.com/312742452
 

charlesdh

Member
Hi,
The error is because you clicked the "LestSearch" 2 times. When the "Listbox" only show 1 employee. You do not need to click the "LetsSearch" again!
Check this file it should show the "Header" when you select "Instant Search"

Step 2 of your post will only show the employee. I think this should be ok. You want the to see the final selection when you click the "LetsSearch". You do not need to highlight the emp for the search.
 

Attachments

Svmaxcel

Member
Hi,
The error is because you clicked the "LestSearch" 2 times. When the "Listbox" only show 1 employee. You do not need to click the "LetsSearch" again!
Check this file it should show the "Header" when you select "Instant Search"

Step 2 of your post will only show the employee. I think this should be ok. You want the to see the final selection when you click the "LetsSearch". You do not need to highlight the emp for the search.
Hats off to you.
I am impressed with your way of coding.

I will check the file and update ASAP.
Thanks again.
 
Top