Windows user name and password for authentication

[tsawyer73]

I'd like to protect a small area in the excel sheet. Example H45:M47 where the user should put his/her windows username and password as authentication method through a VBA userform window.

I'm not a VBA expert due to that fact the full step by step VBA code and setup will appreciate!

Thanks

 

[Kyle123]

Skip the userform, in sheet PR:

Option Explicit
Dim oRng As Range


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    If Not Intersect(Target, Range("H45:M47")) Is Nothing Then '''sets which cell(s) to work with
        'Use the above line to set a range of cells by changing ("A1") to your range say ("B4:D12")
        If Not isPermitted() Then
            MsgBox "You don't have permission to edit here"
            oRng.Select
        End If
    Else
        Set oRng = Target
    End If
    
    
End Sub


Function isPermitted() As Boolean
    If UBound(Filter(Application.Transpose(Sheets("MySecretSheet").Range("A1:A10").Value), Environ("username"))) = 0 Then isPermitted = True
End Function

Just have a list of people who can edit in a sheet named "MySecretSheet"

 

[nuked]

Here you go.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim strResponse As String

If Not Intersect(Target, [H45:M47]) Is Nothing Then

    strResponse = InputBox("Hello " & Environ("username") & ". Please enter your password")
    
        If Not strResponse = "beancounter" Then '<==== choose whatever password you want here
        
            [A1].Select

            MsgBox "Naughty naughty. You are not allowed to change this cell through conventional means"
            
        End If
        
End If
End Sub

 

[tsawyer73]

Skip the userform, in sheet PR:

Option Explicit
Dim oRng As Range


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    If Not Intersect(Target, Range("H45:M47")) Is Nothing Then '''sets which cell(s) to work with
        'Use the above line to set a range of cells by changing ("A1") to your range say ("B4:D12")
        If Not isPermitted() Then
            MsgBox "You don't have permission to edit here"
            oRng.Select
        End If
    Else
        Set oRng = Target
    End If
    
    
End Sub


Function isPermitted() As Boolean
    If UBound(Filter(Application.Transpose(Sheets("MySecretSheet").Range("A1:A10").Value), Environ("username"))) = 0 Then isPermitted = True
End Function

Just have a list of people who can edit in a sheet named "MySecretSheet"

Where can I put this code?

Thanks

 

[nuked]

Where can I put this code?

Thanks

Alt+F11
Double click the sheet on the left hand side that has the protected range on
Paste the code in the big white area on the right hand side

 

[tsawyer73]

Where can I put this code?

Thanks

That code doesn't do anything

 

[tsawyer73]

It's ok, but the method looks like same as my original VBA which has got only 1 fix password instead of list of them

Your method is shorter but the password is fix. How can I add username specific password to the code?

 

[Kyle123]

My code is nothing like yours. IT doesn't need a password since it is based purely on the user - why do you need them to enter a password when you already know who they are?

 

[tsawyer73]

My code is nothing like yours. IT doesn't need a password since it is based purely on the user - why do you need them to enter a password when you already know who they are?

I tried to use your code without any result it was doing nothing I don't know why sorry!

 

[Kyle123]

Did you put it in the right sheet?

 

[tsawyer73]

pls check the file here (https://docs.google.com/file/d/0B0Jd2YDhu80QbW1TZmg3WXA0NDQ/edit?usp=sharing)