Unprotect cell based on Username match

[justme]

Sheets("sheet1").Range("a1").Value = Environ("username")
   
    If Range("a1").Value = Range("H5").Value Then format.cell("c24")Protection=unlocked

I will have someone type in their supervisor's name in H5. When they send the form to their supervisor and A1 matches the entry in H5, I want cell c24 to be unlocked so the supervisor can make an entry.

Thanks

 

[Von Pookie]

If Environ("username") = Range("H5") Then
    Range("C24").Locked = False
Else
    Range("C24").Locked = True
End If

 

[justme]

Thanks so much. It looks just like what I want.

 

[justme]

Hmmm problems.....

Is there a problem when you are using merge cells for the fields?

H5 is actually H5:j5 and c24 is actually c24:e24

 

[lenze]

As a caution, if your sheet is protected, you will need to unprotect it in the code.

Activesheet.UnProtect "password"
If Environ("username") = Range("H5") Then 
    Range("C24").Locked = False 
Else 
    Range("C24").Locked = True 
End If 
ActiveSheet.Protect "password"

lenze

 

[lenze]

Hmmm problems.....

Is there a problem when you are using merge cells for the fields?

H5 is actually H5:j5 and c24 is actually c24:e24

Apparently so!!! I get an Error with merged cells, as is common. VBA doesn't seem to understand them. IMO, merged cells should almost never be used.

Instead of merging your cells, format them as "Center Across Selection", particularly the ones to be unlocked (Format>Cells>Alignment>Horizonal Field). The code should now run.

lenze

 

[justme]

Edit - cells are not merged anymore!

Thanks Lenze, I thought that might be the problem (I did have the worksheet protected). So I put in your code. Then I thought, if the username = the supervisor's name I don't care what cell is unprotected. I can just leave the workbook unprotected and they can write anywhere. Then when the workbook gets mailed back to others, the whole thing is locked back up.

So my code looks like;
Sheets("sheet1").Range("a1").Value = Environ("username")

If Environ("username") = Range("H5") Then
ActiveSheet.Unprotect
Else
ActiveSheet.Protect
End If

End Sub

It isn't working. So, I tried pasting it everywhere thinking it might have to reside in ThisWorkbook or a Sheet module. So far no luck. Any more suggestions?

The way I am testing this is to type my name in the Supervisor cell and see if it will unlock the workbook, then type in any other name to see if it will lock.

 

[lenze]

One or two ways

In the ThisWorkbook module

Private Sub Workbook_Open()
    If Sheet1.Range("$A$1") = Environ("UserName") Then
    Sheet1.Unprotect
    Else
    Sheet1.Protect
    End If
End Sub

In the WorkSheet module

Private Sub Worksheet_Activate()
    If Range("$A$1") = Environ("UserName") Then
    ActiveSheet.Unprotect
    Else
    ActiveSheet.Protect
    End If
End Sub

Both seem to work

lenze

 

[justme]

Lenze,
I used the first example and banged my head all afternoon til I figured out case sensitive. duh!!

it's working great. Thanks alot.

 

[lenze]

Lenze,
I used the first example and banged my head all afternoon til I figured out case sensitive. duh!!

it's working great. Thanks alot.

Been there, done that

You can get around that in VBA by using the UCase method. See help files for examples.

If UCase(Range("$A$1") = UCase(Environ("UserName") Then

lenze