Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Tools > Options (2002)

    I have a protected worksheet in a protected workbook. Is there a way to stop Tools > Options from being accessible?
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tools > Options (2002)

    You can set a password that is required to unprotect the sheet.

    Why would you want to disable Tools | Options...?

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tools > Options (2002)

    The sheet is password protected. There are other sheets that code writes to and from, which means that I cannot hide them, so I turn the sheet tab visibility off in options.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tools > Options (2002)

    > There are other sheets that code writes to and from, which means that I cannot hide them

    Huh? It is perfectly possible to read and write cells in hidden worksheets using code.

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tools > Options (2002)

    I'm probably missing something. I've always used the same technique, setting the sheets visibility to hidden. I always thought that by password protecting the sheet and file, options was not available, so the sheets could not be unhidden. I am most probably wrong.

    Later:, Looking back at some of my older template files, it would appear that I am wrong! How have I missed that??

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tools > Options (2002)

    You can set the Visible property of a worksheet to xlSheetVeryHidden in the Visual Basic Editor or using code. This means that the sheet won't be listed in Format | Sheet | Unhide... It can only be made visible again by setting the Visible property to xlSheetVisible.
    If you set a password on the workbook's Visual Basic project (in Tools | <Projectname> Properties in the Visual Basic Editor), end users have no way of making the sheet visible any more.

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tools > Options (2002)

    Hans, Thanks, I did that. I assume that you cannot select cells, rows, sheets etc in hidden sheets. So is there a way to make this code work?

    Sheets(Array("10", "20", "25", "30", "40")).Select
    Sheets("10").Activate
    Rows("21:1019").Select
    Selection.ClearContents
    Range("D5").Select
    Selection.ClearContents

    Sheets("INPUT SHEET").Select
    Range("E21:H5015").Select
    Selection.ClearContents
    Range("E21").Select

  8. #8
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Tools > Options (2002)

    You don't need to select cells in order to clear them.

    Try commands like
    <code>Sheets("10").Rows("21:1019").ClearContents </code>

    StuartR

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tools > Options (2002)

    Thanks Stuart, I take it that this cannot work on an array like:

    Sheets(Array("10", "20", "25", "30", "40")).Rows("21:1019").ClearContents <img src=/S/question.gif border=0 alt=question width=15 height=15>

  10. #10
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Tools > Options (2002)

    I think you will have to use a loop to clear the sheets one at a time, unless someone cleverer than I can tell you a simpler way.

    StuartR

  11. #11
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tools > Options (2002)

    Stuart is correct (at least I cannot refute his statement). For multiple sheet content clearing, you need to select the sheets in the code, but since the sheets are hidden, you cannot select them.

    Here is a snippet of code you could use to clear the contents.

    sheetArray = Array("10", "20", "25", "30", "40")

    For i = 0 To UBound(sheetArray)
    Sheets(i + 1).Rows("21:1019").ClearContents
    Next

  12. #12
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tools > Options (2002)

    Thanks Mike, but....

    Sheets(i + 1).Rows("21:1019").ClearContents

    =
    Attached Images Attached Images

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Tools > Options (2002)

    Then you will have to unprotect each sheet before clearing and then reprotect the sheets after clearing.

    Steve

  14. #14
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tools > Options (2002)

    I have noticed that I cannot print the hidden sheets either. What is the correct syntax to use to unprotect the sheet? Please.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tools > Options (2002)

    Sheets(i + 1).Unprotect

    and if you have set a password "terces":

    Sheets(i + 1).Unprotect Password:="terces"

Page 1 of 2 12 LastLast

Posting Permissions

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