Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Protecting

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Nanaimo, BC
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've looked, but haven't been able to find the code for unprotecting sheets and workbooks at the beginning of a macro and protecting again at the end. Unfortunately, my help in Excel or VBA isn't installed so I can't look there.
    Also I was wondering if there was a way to make it so cells on the sheet cannot be selected (when you protect the sheet you are still able to select cells).
    Thank You!
    Kyle

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    From recording a macro, the codes for unprotect and protecting a worksheet and workbook are:

    ActiveSheet.Unprotect password:="Testing"
    ActiveSheet.Protect password:="Testing", DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveWorkbook.Protect Structure:=True, Windows:=False
    ActiveWorkbook.Unprotect

    And as for have cells can not be selected, I am hot too sure whether hiding those cells would do the trick. For example, hide the whole column or something, and the having the sheet protected, then users can not edit them.

    HTH

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-24 19:37, kwaring wrote:
    I've looked, but haven't been able to find the code for unprotecting sheets and workbooks at the beginning of a macro and protecting again at the end. Unfortunately, my help in Excel or VBA isn't installed so I can't look there.
    Also I was wondering if there was a way to make it so cells on the sheet cannot be selected (when you protect the sheet you are still able to select cells).
    Thank You!
    Kyle
    Try this;

    Sub Protect_Noselection()
    ActiveSheet.Protect "test", True, True, True
    ActiveWorkbook.Protect "test", True, True
    ActiveSheet.EnableSelection = xlNoSelection

    '=============
    'Do your thing
    '=============

    ActiveSheet.Unprotect "test"
    ActiveWorkbook.Unprotect "test"
    End Sub





    Note XlNoselection will only take effect when the sheet is in protection mode.


    Ivan

Some videos you may like

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
  •