Allow users to edit range on worksheet

LisaM

New Member
Joined
Feb 3, 2011
Messages
12
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?
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top