Excel column-based permissions 1

[fluid11]

I set up an Excel spreadsheet with column-based permissions. Each column specifies users from my Win2K domain that can access each column. This isn't working. If I give access to anybody in a column, anyone who opens the spreadsheet can change that column, regardless of the permissions I set.

Ideally, if this is possible, I would like to have to enter a username and password when you open the spreadsheet. Can you create users in Excel, or do you have to use accounts from a Windows domain or workstation (or elsewhere)?

Any ideas?

Thanks,
Chris

 

[xlbo]

I can't speak for later versions of excel as I havn't used them but certainly in '97, there are no permissions - you would have to use the username to either lock or unlock all cells in certain columns and then protect the sheet.
You could also set the scroll area dependant on the username.
eg Sheets("Sheet1&quot.scrollarea = "$A:$A"

HTH
Geoff

 

[SkipVought]

fluid,

I assume that you have some kind of access table.

Initially ALL cells are locked and sheet is protected

To grant access...

1. unprotect sheet
2. lookup the userid to find access range
3. set the access range locked property to false
4. protect the sheet

On leaving sheet or closing workbook...

1. unprotect sheet
2. set cell.locked = true
3. protect sheet

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[fluid11]

I'm using Excel XP (2002).

Go to Tools - Protection - Allow Users to Edit Ranges. Click New, then select the columns/row/cells you want to assign permissions to, and then the Permissions button. This is where you can select users from the local workstation or from the Windows domain.

I selected users from the domain, but its not working correctly. When I assign a user to a column, it grants everybody access to it when the spreadsheet is locked.

This is a new feature in XP, that isn't working.

 

[fluid11]

This was taken from Excel help...
Please read my comment at the end.



Give specific users access to protected ranges

You must have Windows 2000 to give specific users access to ranges.

1. On the Tools menu, point to Protection, and then click Allow Users to Edit Ranges. (This command is available only when the worksheet is not protected.)

2. Click New.

3. In the Title box, type a title for the range you're granting access to.

4. In the Refers to cells box, type an equal sign (=), and then type a reference or select the range.

5. In the Range password box, type a password to access the range.

The password is optional; if you don't supply a password, any user will be able to edit the cells.

6. Click Permissions, and then click Add.

7. Locate and select the users to whom you want to grant access. If you want to select multiple users, hold down CTRL while you click the names.

8. Click OK twice, and if prompted retype the password.

9. Repeat the previous steps for each range for which you're granting access.

10. To retain a separate record of the ranges and users, select the Paste permissions information into a new workbook check box in the Allow Users to Edit Ranges dialog box.

11. Protect the worksheet: Click Protect Sheet in the Allow Users to Edit Ranges dialog box.

12. In the Protect Sheet dialog box, make sure the Protect worksheet and contents of locked cells check box is selected, type a password for the worksheet, click OK, and retype the password to confirm.

Note A sheet password is required to prevent other users from being able to edit your designated ranges. Make sure you choose a password you can remember, because if you lose the password, you cannot gain access to the protected elements on the worksheet


Red Text[/ color]
#5 says that the password is optional and that if you don't supply a password, any user will be able to edit the cells. If this is true, then whats the point of selecting users if you aren't going to supply a password? I didn't supply a password because I want access to be granted on a per-user basis. I don't want a separate password for each column.



Chris

 

[SkipVought]

fluid,
In access protection, it is SOP to require a password. So, in my opinion, this is NOT "Microsoft behaving badly" as is the case in some situations as we know.

Here's what you might do in code...

1. In the Workbook_Open event, detect the user's id.
2. From your access table, determine the password,
3. Supply the password to the range

this should set the environment for the user Skip,
metzgsk@voughtaircraft.com

 

[fluid11]

Sorry I didn't respond back right away. This project got pushed back for awhile.

Is this possible to do without an Access table? I've never used Access before and I would like to be able to do this with just a single Excel spreadsheet. Is there third party software that will require you to enter a username and password when you open the spreadsheet? That would be ideal where I can then use those usernames to assign read or write access to each column. Is this possible.

Thanks,
Chris

 

[fluid11]

I just found this...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlwebProtSht.asp

Specifically....


Assigning User Permissions to Cell Ranges
Another new protection feature in Excel 2002 is the ability to assign user-level permissions to different regions on a protected worksheet. Within each edit range, you can specify the users who can edit the range without unlocking the entire worksheet. Additionally, you can specify that a user provide a range-specific password in order to make changes to the worksheet.

Each worksheet contains an AllowEditRanges collection that contains the collection of edit ranges for that worksheet. Use the AllowEditRanges property of the Protection object to return the collection of edit ranges. Use the Add method to add an AllowEditRange object to the worksheet.

The list of users for each AllowEditRange object is stored in the UserAccessList collection. The Users property of the AllowEditRange object is used to return the collection of users for the range. Use the Add method to add users to an edit range.

The AddEditRange procedure in the sample download illustrates how to create and add users to an edit range.




Now I have to figure out exactly how to do this as this article doesn't give any examples that I can see.

 

[DaleWatson123321]

Chris,

This might just be a "shot in the dark", BUT, it appears you might be in a position of considering such...

Is there a possibility that...

1) You could "segment" your data into SEPARATE files.

2) These separate files could be password protected, or placed into a separate folder where only certain users are given access permissions.

3) You could incorporate "link" formulas - to link the required data between these separate files and your "master" file that would remain in a "secure" location.

I hope this will at least "get the wheels turning", and hopefully can become a workable solution.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[fluid11]

I can't do that because the file is going to be stored on an FTP server, not a file server. Different versions of the file will be uploaded and downloaded to the server so its important that we keep it a single file.

Thanks for all your replies.
Chris

 

[SkipVought]

Fluid,
I was using access with a small a. Within you Excel workbook is a user access table. When a user opens the file, your program detects the user's ID and sets the workbook/worksheet envirpnment to unprotect the data for that user. Your user access table would contain the attributes that wuold define the data that that user would have access to.

Remember, access, with a small a. Skip,
metzgsk@voughtaircraft.com

 

[fluid11]

Skip,

Thats sounds like its exactly what I need. How do I go about setting that up? Does the user have to type its username when she opens the workbook?

Thanks,
Chris

 

[SkipVought]

fluid,

You probably have a dynamic link library (dll) that contains a Get User ID routine. You might check to see if you have advapi32.dll

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

In you code, you would call it like this...

Function Get_User_Name() As String
    
    Dim lpBuff As String * 25
    Dim ret As Long, UserName As String
    
    On Error GoTo Err_Get_User_Name
    ' Get the user name minus any trailing spaces found in the name.
    ret = GetUserName(lpBuff, 25)
    UserName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
    Get_User_Name = UserName

Exit_Get_User_Name:
    Exit Function
    
Err_Get_User_Name:
    MsgBox "Get_User_Name(): " & Err.Description
    Resume Exit_Get_User_Name
    
End Function

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[fluid11]

Sorry, but I'm really confused now I don't have advapi32.dll on my system.

Where do I type all of this code?

How do you identify yourself to Excel when you open the spreadsheet so that it knows which user you are, after this is all set up?

Thanks,
Chris

 

[fluid11]

Skip,

I'm not a programmer so I don't understand anything you just wrote I found something similar on Microsoft's website, but I'm just as confused with that.



Attribute VB_Name = "Module3"
Sub ProtectMethodAllSheets()

Dim shtCurrent As Worksheet

' Loop through each worksheet in the active workbook
For Each shtCurrent In ActiveWorkbook.Worksheets

' Protect the worksheet. Allow the users to format
' and sort cells.
shtCurrent.Protect Password:="Pass", Contents:=True, _
DrawingObjects:=True, Scenarios:=True, _
AllowFormattingCells:=True, AllowSorting:=True
Next
End Sub

Sub AddEditRange()

Dim erScenarioEditRanges As AllowEditRanges
Dim erFinanceInputs As AllowEditRange

' Add an edit range to the Scenarios worksheet.
Set erFinanceInputs = _
Worksheets("Scenarios&quot.Protection.AllowEditRanges.Add(Title:= _
"Finanacial Scenario Inputs", Range:=Worksheets("Scenarios&quot _
.Range("E1:G15&quot, Password:="ecnanif&quot

' Add Andrew Dixon to the list of users who can change the
' edit range. By setting the AllowEdit argument to False,
' Andrew must enter the designated password in order to make
' changes to the edit range.
erFinanceInputs.Users.Add Name:="Andrew Dixon", AllowEdit:=False

' Add Stephanie Hooper to the list of users who can change the
' edit range.
erFinanceInputs.Users.Add Name:="Stephanie Hooper", AllowEdit:=True

' Protect the Scenarios worksheet. Allow users to format and sort cells.
Worksheets("Scenarios&quot.Protect Password:="OpenSesame", _
DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
AllowSorting:=True, AllowFiltering:=True

End Sub

Sub ListEditRangesAndUsers()

Dim ictr As Integer
Dim rngReportTarget As Range
Dim wbAudit As Workbook
Dim shtCurrent As Worksheet
Dim erCurrentEditRange As AllowEditRange
Dim shtReport As Worksheet

' Set a variable to the active workbook.
Set wbAudit = ActiveWorkbook

' Call a routine that sets up the report
' workbook and return a reference to the first
' worksheet in the new workbook.
Set shtReport = SetupReportSheet

' Set the initial report cell.
Set rngReportTarget = shtReport.Range("A2&quot

' Loop through all of the worksheets in the active workbook.
For Each shtCurrent In wbAudit.Worksheets

' Loop through each edit range in the worksheet.
For Each erCurrentEditRange In shtCurrent.Protection.AllowEditRanges

' Check to see whether any users are assigned to the Edit Range.
If erCurrentEditRange.Users.Count > 0 Then

' Loop through each user.
For ictr = 1 To erCurrentEditRange.Users.Count

' Write the edit range information for each user
' to the report worksheet
With rngReportTarget
.Value = erCurrentEditRange.Title
.Offset(0, 1).Value = shtCurrent.Name & "!" & _
erCurrentEditRange.Range.Address
.Offset(0, 2).Value = erCurrentEditRange _
.Users(ictr).Name
.Offset(0, 3).Value = erCurrentEditRange _
.Users(ictr).AllowEdit
End With
Set rngReportTarget = rngReportTarget.Offset(1, 0)
Next ictr
Else

' Write the information for edit ranges that have
' not been assigned any users to the report worksheet.
With rngReportTarget
.Value = erCurrentEditRange.Title
.Offset(0, 1).Value = shtCurrent.Name & "!" & _
erCurrentEditRange.Range.Address
.Offset(0, 2).Value = "No Users"
End With
Set rngReportTarget = rngReportTarget.Offset(1, 0)
End If
Next erCurrentEditRange
Next shtCurrent
End Sub

Function SetupReportSheet() As Worksheet
Dim shtReport As Worksheet

Set shtReport = Workbooks.Add.Worksheets("sheet1&quot

With shtReport
.Range("A1&quot.Value = "Range Title"
.Range("B1&quot.Value = "Address"
.Range("C1&quot.Value = "User Name"
.Range("D1&quot.Value = "Edit Without Password?"
.Range("A11&quot.Font.Bold = True
End With

Set SetupReportSheet = shtReport
End Function

 

[SkipVought]

fluid,
This came right out of Microsoft...

XL7: Visual Basic Procedure To Get Current User Name (Q152970)

Although this references to xl97, it applise to later versions. I am running Windows 98 with Excel 2000. I have this dll in my C:\WINDOWS\SYSTEM folder.

You ought to check your installation of Office.

Now -- where does this code go.
1. The declaration goes in a module in the (General) Object, (Declarations) Procedure area (in other words, at the TOP of you module.

2. The function goes anywhere below the declaration in that module

3. In the Workbook Object Workbook_Open event, you want to call the function to get the user name

   ....
   UserName =  Get_User_Name
  ' now that you have the user name, set the environment accordingly
   Select Case UserName
      Case "John Doe"
         'set up what John Doe can see/change
      case "Mary Roe"
         'set up what Mary Roe can see/change
      ...
   End Select
   ....

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[xlbo]

I know Skip's function for getting the username works - I have used precisely the same in the past....however.....if you are on NT (can't speak for other networks 'cos it's the only one I've known :-( )
myUserName = environ("username&quot

will give you the username of the current user.
HTH
Geoff