Worksheet Range Protection
Worksheet Range Protection
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Worksheet Range Protection

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I'm trying to protect a certain range of cells in a worksheet from within Access.

    My code is below:

    Dim objExcel As Excel.Application
    Dim objWB As Excel.Workbook
    Dim objWS As Worksheet
    Dim intColumn As Integer
    Dim intRow As Integer
    Dim intRecords As Integer

    Set objExcel = Excel.Application
    objWS.Unprotect
    objWS.Protection.AllowEditRanges.Add Title:="Classified", Range:=Range("$A$2:$D$65536"), Password:=""

    objWS.Columns.AutoFit
    objWS.Protect
    objWB.SaveAs strFileName

    I get a method or data member not found on the objws.protection line.

    I am referencing the Excel 8.0 object library.

    Can anyone help?? I'd really appreciate it. I'm new to the Excel object model!

    Thanks!
    Jeanine Scott

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-07 08:16, jscott wrote:
    I'm trying to protect a certain range of cells in a worksheet from within Access.

    My code is below:

    Dim objExcel As Excel.Application
    Dim objWB As Excel.Workbook
    Dim objWS As Worksheet
    Dim intColumn As Integer
    Dim intRow As Integer
    Dim intRecords As Integer

    Set objExcel = Excel.Application
    objWS.Unprotect
    objWS.Protection.AllowEditRanges.Add Title:="Classified", Range:=Range("$A$2:$D$65536"), Password:=""

    objWS.Columns.AutoFit
    objWS.Protect
    objWB.SaveAs strFileName

    I get a method or data member not found on the objws.protection line.

    I am referencing the Excel 8.0 object library.

    Can anyone help?? I'd really appreciate it. I'm new to the Excel object model!

    Thanks!
    Jeanine Scott
    In Excel, to protect a specific range of cells, you first need to "lock" the cells and then protect the worksheet.

    So something like this should work in your case:


    objWS.Range("$A$2:$D$65536").Locked = True
    objWS.Protect PassWord:="", DrawingObjects:=True, Contents:=True, Scenarios:=True


    This code would be instead of your two protect lines.

    HTH

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks, Mark. I did try this - however what appears to happen is that the entire worksheet becomes protected.

    I only want a certain range protected which is why I went in the AllowEditRanges direction. When I take the protection off the locked cells are editble.

    What am I doing wrong?

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-07 09:03, Mark O'Brien wrote:
    Yeah, my bad. MS locks all of the cells be default.

    Try this before locking the range:


    objWS.UsedRange.Locked = False


    This will unlock any cells that have data in them. If you're not constantly adding data then you can do this each time. If you are then I would suggest something like this:



    objWS.Range("A1:IV65536").Locked = False



    To unlock all of the cells on the sheet the first time you run.

    I'm not sure of the difference in runtime for these two pieces of code, but I would stick to the first one if I had a choice.


    Also, the only way to protect individual cells is to lock them and then protect the sheet. So if the sheet isn't protected, the locked cells aren't protected. As far as I know, this is a limitation in Excel.

    In your code, you might just have to protect the sheet again at the end.

  5. #5
    Guest

    Default

    hmmm...
    I did get this to work exactly the way I wanted it to in Excel by utilizing the "Allow Users to Edit Ranges" option on the Protection menu, then protecting the sheet. It protects my range while at the same time allowing the other cells to be edited.

    Can you not do this via code?

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry! forgot to put my username on the post...not trying to be anonymous


    On 2002-03-07 09:16, Anonymous wrote:
    hmmm...
    I did get this to work exactly the way I wanted it to in Excel by utilizing the "Allow Users to Edit Ranges" option on the Protection menu, then protecting the sheet. It protects my range while at the same time allowing the other cells to be edited.

    Can you not do this via code?

  7. #7
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Are you using Excel XP?

    I've never used that and this could be a new feature in it. If it is, then it's long overdue.

    To find out what the VBA is, just use the macro recorder to record your actions and look at the code generated.

  8. #8
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yup,

    you're using 2002.

    Here's a link that'll be useful to you, I think it answers your question:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl10/html/xlproAllowEditRanges.asp>

    Actually, no it doesn't solve your problem. What would solve your problem is going back to your original code then removing the unprotect line from your code. If you unprotect your worksheet, then all users can edit the sheet as they see fit. So the error generated is basically telling you that this line


    objWS.Protection.AllowEditRanges.Add Title:="Classified", Range:=Range("$A$2:$D$65536"), Password:=""



    is nonsense, because there's no protection on the sheet once it's unprotected, hence the method fails.

    I hope this solves the problem. I really, really do.

    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-03-07 09:40 ]

    [ This Message was edited by: Mark O'Brien on 2002-03-07 09:47 ]

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ahh! that explains it. I have to use the Excel 8.0 object library since my clients have Excel97. However I have Excel 2002 on my machine - so of course the 8.0 library won't recognize the 2002 properties. Well, that sucks - to put it bluntly.

    Guess it's just going to have to be "one of those things"

    Thanks for your help!

  10. #10
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-03-07 09:45, jscott wrote:
    Ahh! that explains it. I have to use the Excel 8.0 object library since my clients have Excel97. However I have Excel 2002 on my machine - so of course the 8.0 library won't recognize the 2002 properties. Well, that sucks - to put it bluntly.

    Guess it's just going to have to be "one of those things"

    Thanks for your help!
    Tell your clients to spend a couple of hundred (bucks, quid, yen) whatever on XL2002 if they really want it. I'm guessing this is a small percentage of the cost of any work they're getting you to do.

    I've done this before going from XL97 and XL2000 when I hit problems with new features.

    Although, they sound pretty cheap if they're still on 97.
    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-03-07 09:55 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com