Unprotect/Protect Limited Range for New User

[yunesm]

- I have a spreadsheet that has a data entry tab that is protected (sheet1)
- I am the admin and currently the only one who can unlock the tab for data entry
- I need allow another user to data enter only on just one sheet (sheet1)
- The data entry occurs within a range (named “DERANGE”)

- I need a macro that
1) Requires a user password (i.e. “access”)
2) If password not entered correctly they would get a message (unauthorized user ect…)
3) If password entered correctly the macro would:
a. unlock only the data entry range (“DERANGE”) of sheet1 (with admin password)
b. re-set the original protection of sheet1 before closing (event macro if there have been any changes in the spreadsheet since opening?) (This part seems tricky to me)

I'm open to any suggestions on how to handle this...
Thanks!!!

[Prasad.mani]

Try this

Private Sub CmdOK_Click()
On Error GoTo HandleError
If (TextBox1.Value = "password") Then
test.Range("A7:A10").Select
Selection.EntireRow.Hidden = False
sheet1.Range("A1").Select
TextBox1.Value = ""
Else
sheet1.Range("A7:A10").Select
Selection.EntireRow.Hidden = True
MsgBox "Password does not match"
TextBox1.Value = ""
TextBox1.Activate
End If
Exit Sub
HandleError:
End Sub