VBA to require password to filter

[gmittar]

Hi All,

I have a simple sheet of names and roles that I need to send out to about 60 different people. The sheet has all data filtered out, and the recipient selects their organization from a drop down filter, which shows their portion of the sheet. This all works well as is.

What I would like to do is to protect the sheet so that when they select an org from the dropdown, they are prompted for a password. Further, I would like this password to be unique to their org. So I'd like to have a list of orgs and their corresponding passwords that I will superhide. So, select Org #16 , require password #16 .

Any ideas about how to accomplish this?

Thank you in advance

 

[gmittar]

This might be a better illustration of what i'm trying to do. The below screen grab (I don't have attach permissions) represents both tabs that I have, but ultimately, the Org/PW lists will be on a superhidden tab.

I'm planning to have the data on the data tab completely hidden (I have that part worked out), with the user selecting their org from the drop down up top. When they select their org, I want to prompt them for a password, which will then compare the selected org to the list and then check the corresponding password. If the password matches, the filter will show the data for the org they've selected. If not, it will give an error message.

I know this is possible as I've found similar code via google, but I'm just not good enough to do the rework. I apologize for the plain paste, I haven't yet figured out how to property attach items here. Any help is appreciated.

[TABLE="width: 704"]
<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Org name[/TD]
[TD="width: 64"]PW[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]Org 1[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]Org 1[/TD]
[TD="align: right"]42[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Org 2[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD]Org[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[/TR]
[TR]
[TD]Org 3[/TD]
[TD="align: right"]79[/TD]
[TD][/TD]
[TD][/TD]
[TD]Org 1[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]Org 4[/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD][/TD]
[TD]Org 2[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]88[/TD]
[/TR]
[TR]
[TD]Org 5[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]Org 3[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]Org 6[/TD]
[TD="align: right"]44[/TD]
[TD][/TD]
[TD][/TD]
[TD]Org 4[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]78[/TD]
[/TR]
[TR]
[TD]Org 7[/TD]
[TD="align: right"]47[/TD]
[TD][/TD]
[TD][/TD]
[TD]Org 5[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD]Org 8[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]Org 6[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD]Org 9[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD]Org 7[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD]Org 10[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Org 8[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]92[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Org 9[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Org 10[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[mumps]

This forum doesn't allow the attachment of files. If you want to post your file, you could upload a copy of your file which includes the macros you are currently using, to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[gmittar]

Thanks for the info. Attached is a sample file with the macros that I'm currently using to filter the data based on the dropdown.

I would like a password prompt to pop up when the filter is used, and then if the password matches per the org list, filter the data.

https://sagehospitalityllc.box.com/s/jtabpqvqpnwwb816tezusozfbvcablfm

Thank you in advance.

 

[mumps]

Start by unlocking cell A1 in the "Data" sheet. Hide all the rows from row 2 down. Protect the sheet using the password "MyPassword". Copy and paste the macro below into the worksheet code module. Do the following: right click the tab for your "Data" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. You can change the password to one of your choosing. If you do, then change all occurrences of "MyPassword" in the macro to match the one you chose. Close the code window to return to your sheet. Make a selection in the drop down list in cell A1.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim lastRow As Long, pw As Range, response As String
    lastRow = Columns(1).Find(what:="*", after:=Cells(Rows.Count, "A"), LookIn:=xlFormulas, lookat:=xlPart, searchdirection:=xlPrevious).Row
    response = InputBox("Please enter your password.")
    If response = "" Then
        MsgBox ("You have not entered a password.")
        Application.ScreenUpdating = True
        Exit Sub
    Else
        Set pw = Sheets("Users").Range("A:A").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
        If Not pw Is Nothing And pw.Offset(0, 1) = response Then
            ActiveSheet.Unprotect Password:="MyPassword"
            Range("A3:G" & lastRow).AutoFilter Field:=1, Criteria1:=pw
            ActiveSheet.Protect Password:="MyPassword", DrawingObjects:=True, Contents:=True, Scenarios:=True
            ActiveSheet.EnableSelection = xlUnlockedCells
        Else
            MsgBox ("You have entered an invalid password.")
            Application.ScreenUpdating = True
            Exit Sub
        End If
    End If
    Application.ScreenUpdating = True
End Sub

 

[gmittar]

Thanks Mumps, that works very well! Appreciate the help

 

[mumps]

You are very welcome.

 

[gmittar]

Hi mumps,

One thing I'd like to add to this macro. If I want to show all the orgs, how do I accomplish that?

Thanks!

 

[mumps]

Click here to download your file.

I have changed the Data Validation formula to refer to range A2:A12 in the Users sheet and have added "All" to the drop down list. When you choose "All" from the dropdown list, enter "ShowAll" to the prompt asking for the password. You can change the password to something of your choosing by changing it in the macro.

 

[gmittar]

Works great, thanks again!