EXECEL-VBA FILTER

0

i got a problem on showing data on a listbox on userform.

i want tha data filtered on the sheet and show it to the userform listbox.

the criteria of filtering the data will depend on a userform login, that saves the username that is logged to range E2 of a sheet.

i just want the user to log in, and then allow that user to edit data specific for that user, and an admin username that allows to edit data for all users.

heres a part of the sheet

#     username              id                 date

1     arobingayan           31500         07/06/2016

2     asaustria                31501         07/02/2016

3     ambalin                  31502         07/01/2016

4     mcnones                31865         07/12/2016

5     radelosreyes          31000         07/01/2016

it is a multicolumn data consist of 34 columns, with a header, is it possible using execel and vba?

so when i logged as ASAUSTRIA, in userform listbox it will display data that is assigned to ASAUSTRIA respectively, and DVPALLERA will be the admin account that can view all accounts.

tnx

Answer
Discuss

Discussion

Nice username! :)
don (rep: 1247) Jul 20, '16 at 8:49 pm
Ok, so do you already have the login setup and you only need the filter ability or you need everything? - a login form/window; and showing different data depending on who is logging-in.
don (rep: 1247) Jul 20, '16 at 8:52 pm
yes i have the log in userform set, and it will record the username logged at A2 and the time and date on B2, and will display the currently logged username on E2 in a sheet named "log in". is my log in form okay or i have to redo it to show different data to whom is logged? tnx
deadpool2016 Jul 20, '16 at 11:42 pm
@don basically the user names are based on initials of the employees, like for "mcnones" is from "maria c. nones", and likes so for the rest.
deadpool2016 Jul 20, '16 at 11:45 pm
If the login form works for you then keep it that way. Just remember that Excel is NOT a secure application so nothing that is sensitive should be stored in the document.
don (rep: 1247) Jul 21, '16 at 12:02 am
Are you sure that you want the specific data to appear in a Userform listbox and not within the spreadsheet?

Also, to get any accurate help, you will need to include a copy of the workbook, just put sample data in there instead of the real stuff. Otherwise, I can only tell you general concepts, like how to populate a listbox in a userform.
don (rep: 1247) Jul 21, '16 at 12:04 am
would you guide me on populating the listbox?
deadpool2016 Jul 25, '16 at 6:43 am
I updated my answer. It should now do everything that you need.

If it works, don't forget to select it!
don (rep: 1247) Jul 25, '16 at 1:52 pm
tnx, it worked out fine, but icant go through with the admin account that will access all data, lets say that dvpallera is the admin account, which cant be found on "Column B" how can i populate the listbox for this admin account.
deadpool2016 Jul 25, '16 at 7:56 pm
Which accounts or usernames do you want to be the "admin" accounts?
don (rep: 1247) Jul 25, '16 at 8:08 pm
for the admin account, DVPALLERA


i got errors on clicking/using mouse scroll inside list box
Run-time error '-2147024809 (80070057)':
Could not get Column property. invalid argument

also:
if i reach "ListBox1.List(record_count, 10) = Cells(user_record.Row, 11).Value"

If user_record.Value = "AMBALIN" Then
        ListBox1.AddItem
        ListBox1.List(record_count, 0) = Cells(user_record.Row, 1).Value
        ListBox1.List(record_count, 1) = Cells(user_record.Row, 2).Value
        ListBox1.List(record_count, 2) = Cells(user_record.Row, 3).Value
        ListBox1.List(record_count, 3) = Cells(user_record.Row, 4).Value
        ListBox1.List(record_count, 4) = Cells(user_record.Row, 5).Value
        ListBox1.List(record_count, 5) = Cells(user_record.Row, 6).Value
        ListBox1.List(record_count, 6) = Cells(user_record.Row, 7).Value
        ListBox1.List(record_count, 7) = Cells(user_record.Row, 8).Value
        ListBox1.List(record_count, 8) = Cells(user_record.Row, 9).Value
        ListBox1.List(record_count, 9) = Cells(user_record.Row, 10).Value
        ListBox1.List(record_count, 10) = Cells(user_record.Row, 11).Value


Run-time error '380':
Could not set the List property. Invalid property value

this happen
deadpool2016 Jul 25, '16 at 11:11 pm
hmmmm, I will look into it when I can this week. Might not be able to get back to you tomorrow.
don (rep: 1247) Jul 25, '16 at 11:32 pm
Add to Discussion

Answers

0

Ok, I got your workbook but you need to look into protecting your data before anything.

NOTHING IMPORTANT should be "hidden" in Excel. Excel is not a database and just about any user can access all of the data in the workbook with ease or with a simple google search and a little patience.

When I first opened the file I disabled all macros and got access to all of the data. When I enabled macros, I closed the form and canceled saving the file and got access to all of the data.

That said, if this is very important data, you need to be using Access or another database that has more powerful control systems integrated into it.

If you do want to keep all of this in Excel, first read this tutorial on requiring users to enable macros.

You need to follow those steps in the tutorial to require enabling macros, this will help keep your data secured.

After that, we can populate the userform listbox with the data you want.

Populate the Listbox with Rows of Data

Find the section of your code that starts like this:

Private Sub UserForm_Initialize()

You can view the form in the VBA editor window and then double-click it and  find it in the window that opens.

Go down and replace this line:

ListBox1.List = Sheets("2016").Range("A2:AH" & [a65536].End(3).Row).Value

with this:

Set record_range = Sheets("2016").Range("B1:B30000")
record_count = 0

For Each user_record In record_range

    If user_record.Value = "AMBALIN" Then

        ListBox1.AddItem
        ListBox1.List(record_count, 0) = Cells(user_record.Row, 1).Value
        ListBox1.List(record_count, 1) = Cells(user_record.Row, 2).Value
        'copy the last line and add 1 to the numbers in it for each column that you want

        record_count = record_count + 1

    End If

Next user_record

Follow the instructions from the comment and simply add this line:

ListBox1.List(record_count, 1) = Cells(user_record.Row, 2).Value

for as many columns as you want to appear, add 1 to the numbers that appear in the line of code each time you add it. The pattern is clearly visible in the code above.

Discuss

Discussion

ok, just did the data securing part.

im also planning to have an ms access database but im not familiar with the coding, really, so for now i just want it to be in excel. datas on the project are not that important. just the restricting access and editing of data for specific username is important. and if it can handle multiple user, adding and editing of data at the same time.

but if you could guide me in going to ms access database for this project, it would be a lot of help.
deadpool2016 Jul 22, '16 at 3:03 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login