How do I password protect a drop down selection?

[suzette0735]

I have a drop down list for different departments and each show different information. I want to password protect the "Accounting Copy" selection so that only the accounting department can see all the costs and mark up info. Is there a way to do this?
Thank you!

 

[kevin9999]

It's possible with VBA, but you'd need to password protect your macro as well otherwise it would be easy to 'crack' the protection. Stage 1 would be a worksheet change event macro in the sheet code area of the sheet with the dropdown (right-click the sheet tab name, select View Code, and paste the code into the window that appears on the right of the screen). Adjust the cell reference of the dropdown, and the actual password required.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A1"), Target) Is Nothing Then   '<-- *** Change "A1" to your dropdown cell
        If Not Target.Value = "Accounting Copy" Then Exit Sub
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        p = Application.InputBox("Enter the password")
        If p <> "password" Then Application.Undo            '<-- *** Change "password" to your actual password
        Application.EnableEvents = True
    End If
End Sub

Stage 2 is to password protect your code:
In the VBA Editor select Tools/VBAProject Properties/Protection tab.
Check the Lock project for viewing box
Enter & confirm password (whatever password you like, doesn't have to be the same as in the above code)
Click OK

When you save the file as macro-enabled, the next time you open the file you will be asked for a password to view the code (and as such, see what the Accounting Copy password has been set as).

 

[suzette0735]

It's possible with VBA, but you'd need to password protect your macro as well otherwise it would be easy to 'crack' the protection. Stage 1 would be a worksheet change event macro in the sheet code area of the sheet with the dropdown (right-click the sheet tab name, select View Code, and paste the code into the window that appears on the right of the screen). Adjust the cell reference of the dropdown, and the actual password required.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A1"), Target) Is Nothing Then   '<-- *** Change "A1" to your dropdown cell
        If Not Target.Value = "Accounting Copy" Then Exit Sub
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        p = Application.InputBox("Enter the password")
        If p <> "password" Then Application.Undo            '<-- *** Change "password" to your actual password
        Application.EnableEvents = True
    End If
End Sub

Stage 2 is to password protect your code:
In the VBA Editor select Tools/VBAProject Properties/Protection tab.
Check the Lock project for viewing box
Enter & confirm password (whatever password you like, doesn't have to be the same as in the above code)
Click OK

When you save the file as macro-enabled, the next time you open the file you will be asked for a password to view the code (and as such, see what the Accounting Copy password has been set as).

Thank you so much for your quick reply! This is awesome!! So glad it is possible and for your help! I am very new to VBA and I had already input a worksheet change for the drop downs. Is there a way to have both or maybe I did not do the drop downs correctly? Here are both codes I just tried to do but received an error:

 

[kevin9999]

I can't test this, because I don't have access to your other modules (selectsheet etc.) but try this on a copy of your workbook:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("F2"), Target) Is Nothing Then
        Application.EnableEvents = False
        Select Case Target.Value
            Case "Select Sheet": selectsheet
            Case "Estimate/Bid Sheet": estimatebidsheet
            Case "Warehouse/Installer Copy": warehouseinstaller
            Case "Accounting Copy"
                Application.ScreenUpdating = False
                p = Application.InputBox("Enter the password")
                If p <> "LOGANAVERY" Then
                    Application.Undo
                    Application.EnableEvents = True
                    Exit Sub
                Else
                    accting
                End If
        End Select
        Application.EnableEvents = True
    End If
End Sub

 

[suzette0735]

I can't test this, because I don't have access to your other modules (selectsheet etc.) but try this on a copy of your workbook:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("F2"), Target) Is Nothing Then
        Application.EnableEvents = False
        Select Case Target.Value
            Case "Select Sheet": selectsheet
            Case "Estimate/Bid Sheet": estimatebidsheet
            Case "Warehouse/Installer Copy": warehouseinstaller
            Case "Accounting Copy"
                Application.ScreenUpdating = False
                p = Application.InputBox("Enter the password")
                If p <> "LOGANAVERY" Then Application.Undo
                    Application.EnableEvents = True
                    Exit Sub
                Else
                    accting
                End If
        End Select
        Application.EnableEvents = True
    End If
End Sub

This is what popped up:

 

[suzette0735]

This is what popped up:
View attachment 98552

The other code i had in there was for a macro to run when each drop down is selected? Will this code above also be able to keep that macro and have a password? Thanks!!!!!!!!!!!

 

[kevin9999]

Should do - I just edited the code in post #4. Try it now.

 

[suzette0735]

Should do - I just edited the code in post #4. Try it now.

YOU ARE A GENIUS!!!!!!!!!!!!! Thank you so much!!!

 

[kevin9999]

YOU ARE A GENIUS!!!!!!!!!!!!! Thank you so much!!!

I can assure you Suzette, I am no genius, but thank you for the kind feedback. I'm just happy it works for you