+ Reply to Thread
Results 1 to 5 of 5

Trying to edit user permissions under "Allow user to edit ranges" using VBA

Hybrid View

Whizbang Trying to edit user... 09-02-2009, 04:32 PM
Whizbang Re: Trying to edit user... 09-02-2009, 05:12 PM
Leith Ross Re: Trying to edit user... 09-02-2009, 05:19 PM
Whizbang Re: Trying to edit user... 09-02-2009, 05:25 PM
romperstomper Re: Trying to edit user... 09-02-2009, 05:33 PM
  1. #1
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Trying to edit user permissions under "Allow user to edit ranges" using VBA

    Ok, so I have this bit o' code by recording a macro

        ActiveSheet.Protection.AllowEditRanges.Add Title:="Rage1", Range:=Range( _
            "A2")
    The only problem is that, while recording, I also set permissions specific to a user by:
    1:Clicked on "Permission"
    2: clicked "Add".
    3:The object type and location were already entered so I entered the user id and hit ok.

    This part does not reflect in the code. Does anyone know of a way to add permissions with VBA?
    Last edited by Whizbang; 09-02-2009 at 05:19 PM.

  2. #2
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Trying to edit user permissions under "Allow user to edit ranges" using VBA

    Nevermind. I figured it out.

    ActiveSheet.Protection.AllowEditRanges(1).Users.Add "User", False
    This site gave me the info I needed:
    http://books.google.com/books?id=gkP...age&q=&f=false

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Trying to edit user permissions under "Allow user to edit ranges" using VBA

    Hello WhizBang,

    Here is macro taken from the VBA Help Files. This will list the permissions assigned to a document (Excel Workbook).
    Sub CheckPermissions()
        Dim irmPermission As Office.Permission
        Dim irmUserPerm As Office.UserPermission
        Dim strIRMInfo As String
        Set irmPermission = ActiveWorkbook.Permission
        If irmPermission.Enabled Then
            For Each irmUserPerm In irmPermission
                strIRMInfo = strIRMInfo & irmUserPerm.UserId & vbCrLf & _
                    " - Permissions: " & irmUserPerm.Permission & vbCrLf & _
                    " - Expiration Date: " & irmUserPerm.ExpirationDate & vbCrLf
            Next
            MsgBox strIRMInfo, _
                vbInformation + vbOKOnly, "IRM Information"
        Else
            MsgBox "This document is not restricted.", _
                vbInformation + vbOKOnly, "IRM Information"
        End If
        Set irmUserPerm = Nothing
        Set irmPermission = Nothing
    
    End Sub
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  4. #4
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Trying to edit user permissions under "Allow user to edit ranges" using VBA

    Thank you Leith Ross. I get a Runtime Error '445': Object doesn't support this action

    I've got the adding of permssions figured out. Now I would like to delete them. I've tried:

    ActiveSheet.Protection.AllowEditRanges(1).Users.delete "User"
    ActiveSheet.Protection.AllowEditRanges(1).Users.remove "User"
    ActiveSheet.Protection.AllowEditRanges(1).Delete.Users "User"
    I just can't seem to get it to delete a user, once they've been added.

  5. #5
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,108

    Re: Trying to edit user permissions under "Allow user to edit ranges" using VBA

    Try:
    ActiveSheet.Protection.AllowEditRanges(1).Users("User").Delete
    Everyone who confuses correlation and causation ends up dead.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1