-
May 29th, 2015, 07:26 AM
#1
Thread Starter
New Member
Filter listbox using combobox
Hi,
I have a form with a combobox filled with usernames which are read from a column.
and also an item from combobox is selected (current user).
Code:
Dim user
Dim RangeUsernames As Variant
Dim PName As Variant
Dim num As Integer
Dim userNumOnCombo As Integer
user = Sheets("User_Logins").Range("F7").Value
'Define last Row with data
Set WS = Worksheets("Users_Table")
With WS
Set LastCellA = .Cells(.Rows.Count, "A").End(xlUp)
End With
If LastCellA.row = 10 Then
RangeUsernames = "A10"
Else
RangeUsernames = "A10:A" & LastCellA.row
End If
num = 0
For Each PName In Sheets("Users_Table").Range(RangeUsernames)
ComboBox1.AddItem (PName)
ComboBox1.ListIndex = num
If PName = user Then
userNumOnCombo = num
End If
num = num + 1
Next PName
'Currect user selected on Combobox
ComboBox1.ListIndex = userNumOnCombo
I have also a ListBox1 and I would like to populate the listbox with info about users (4 columns)
which are saved on Sheets("User_Logins") Range("A10" & LastCellA.row) using the combobox.value.
The range has values like
Username1 Day1 Date1 Hour1
Username2 Day1 Date1 Hour2
Username1 Day2 Date2 Hour3
....
I populate the values
Code:
With ListBox1
.ColumnHeads = True
.ColumnCount = 4
.ColumnWidths = "80;80;80;50"
.RowSource = Sheets("User_Logins").Range("A10:D" & LastCellA.row).Address
End With
but I don't know how can I use the combobox to filter the data.
I would like to write a message like "No DATA FOUND " if user has no rows with login.
Finally, how can I have a choice 'All' in combobox for showing data from all users.
I appreciate your help
Thanks a lot
-
May 29th, 2015, 05:10 PM
#2
Re: Filter listbox using combobox
Finally, how can I have a choice 'All' in combobox for showing data from all users.
put it in a (top) cell in rowsource, or use a checkbox to override the combobox
but I don't know how can I use the combobox to filter the data.
there may be other methods, but the 2 that spring to mind are:-
sort the table by username, then find the first and last for the selected user, use as rowsource for listbox
or use a SQL query with ADO to create a recordset where user = combobox.text, additem each record from recordset, which would be my preference
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|