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]

What isn't working? That works fine for me - I can't change anything in the yellow box because my username is not in MySecretSheet

If I put my username in MySecretSheet, I can edit inside the yellow box

The code won't appear to do anything if you are an authorised user - your username is in MySecretSheet

 

[RhodEvans]

Just came acroos this thread and the code is brilliant. I've been trying to come up with a way of only letting certain users alter spreadsheets without having to have an annoying Read only Password. That people have to keep secret and I have to change if it gets known. As everyone who shares the spreadsheets I use have to log on with a username and password, just having it check the username seems plenty secure for me. Thank you very much for your brilliance.

 

[Kyle123]

Hi Rhod, glad it helped

 

[tsawyer73]

Hi Rhod, glad it helped

OK, I double checked and the code works perfectly. Base don your code I just need to collect and identify all of "admin" user's username.

Thanks

 

[tsawyer73]

sometimes the debugger give me errror with reference to this code " oRng.Select"

 

[RhodEvans]

I was about to post the same issue. I get a Run time error 91 " Object variable or with block variable not set" This occurs on the oRng.Select line which is after the unauthorised user clicks OK to the message box. If you are authorised you get no error message.

 

[tsawyer73]

right, if the user authenticated there wasn't any error otherwise the debugger gives me the error message

 

[Kyle123]

Try:

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"
            If oRng Is Nothing Then Set oRng = Range("A1")
            oRng.Select
        End If
    Else
        Set oRng = Target
    End If
    
    
End Sub

 

[tsawyer73]

perfect. The error message has gone and at the end of unauthenticated process the user goes to A1 cell!

Thanks

 

[tsawyer73]

Try:

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"
            If oRng Is Nothing Then Set oRng = Range("A1")
            oRng.Select
        End If
    Else
        Set oRng = Target
    End If
    
    
End Sub

it works on windows machine but how can I get username on Macintosh machine?