Allow users to edit range on worksheet

[LisaM]

I am a new poster, but have been reading this forum for a while now. Friend from work pointed me to it. My level of excel is limited to basic functions, finally figured out vlookup, and simple macros.

My query is this? I have a workbook and within it a worksheet with names, addresses, vendor codes, dates etc. Conditional formatting is used to highlight when some one data expires. Primarily that is what this workbook is used to track, expirations.

However, we have added two new columns for a different department and have protected the workbook on the server. What we would like to see happen, department 'A' can edit all of the workbook while user in department 'B' can only edit a range (say d2:e1000) on the worksheet titled 'Master'.

I am using Excel 2003 and have gone into the toolbar and selected Tools - protection - allow users to edit ranges ... and have selected New - named my range - selected my range - and entered password (*). I have selected the user based from thier network ID and saved the document. I then protected the worksheet. I thought I was good. I was wrong.

The user in department 'B' is still able to open the workbook and select any cell and edit. They should only be able to edit those two columns. What did I miss? Is there a better way to do this? Is it possible when openning excel a box appear and the persons that are admins enter a password granting them all access while another user could enter user for the login ID and a different password and be granted the limited access I described above? Whats the best solution?

 

[Trevor G]

I would set some code up in the workbook open event so you ask the user to enter a password depending on which password they enter depends on what they can do.

Something like this

Private Sub Workbook_Open()
Dim strPass As String
Dim strPass1 As String
Dim strInput As String
strInput = InputBox("Please enter a password", "Sample")
strPass = "Admin"
strPass1 = "User"
If strInput = strPass Then
Sheets("Sheet1").Select
Range("A1:A5").Select
Selection.Locked = False
Selection.FormulaHidden = False
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False
ElseIf strInput = strPass1 Then
Sheets("Sheet1").Select
Range("A1:A20").Select
Selection.Locked = False
Selection.FormulaHidden = False
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False
Else
MsgBox "Password not valid contact the Administrator", vbInformation, "Sample"
End If
End Sub