How do I use VB to set permissions for cell ranges in Excel 2K3?

[Guest]

I'm trying to use a macro / Visual Basic to select a cell's contents
(contains the Windows user ID) and then set permissions on a specific range
of cells so that only that user can edit the cells. I can do this manually
using Tools>Protection>"Allow Users to Edit Ranges . . ." dialog box. But, I
need to do this for 100 users and repeat it every month, so I'd like to use a
script. I can define and name the ranges and set the passwords using a
script in Excel, but can't figure out how to access the "Permissions"
settings to specify users. Anyone have any idea?

 

[David Lloyd]

Chris:

You can use the Users property of the AllowEditRange class to specify the
users. For example:

Public Function AddEditRange()
Dim aer As AllowEditRange

Set aer = ActiveSheet.Protection.AllowEditRanges.Add(Title:="Range1",
Range:=Range("A1"))
aer.Users.Add "Thomas Jefferson", True

Set aer = Nothing

End Function

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I'm trying to use a macro / Visual Basic to select a cell's contents
(contains the Windows user ID) and then set permissions on a specific range
of cells so that only that user can edit the cells. I can do this manually
using Tools>Protection>"Allow Users to Edit Ranges . . ." dialog box. But,
I
need to do this for 100 users and repeat it every month, so I'd like to use
a
script. I can define and name the ranges and set the passwords using a
script in Excel, but can't figure out how to access the "Permissions"
settings to specify users. Anyone have any idea?