+ Reply to Thread
Results 1 to 12 of 12

worksheet protect and macros

  1. #1
    Registered User
    Join Date
    08-30-2013
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    6

    worksheet protect and macros

    Hi all,

    I am new to this forum and a novice with excel vba/macro. With the help of this forum and other websites I have managed to develop a code to automate some simple tasks.
    To be specific I have 3 worksheets. One input worksheet, one output sheet and one sheet where I do all my intermediate calculations. User is required to input in the input sheet and clicking on some buttons (in the input sheet) will perform calculations and report output.
    Now I would like the end user to not be able to mess with the calculation worksheet, so I protect the worksheet. After I protect with a password, my code errors out at "Sheets("CALC").Select" where CALC is the name of my worksheet where I do the intermediate calculation. So it appears as if password protecting the sheet prevents the sheet from even being accessed.

    The error I get is Run-time error '1004': Select method of Worksheet class failed

    Code runs fine if I leave everything unprotected.

    Since my file containts some proprietary information I am unable to attach it as is. If however this is a specific problem, I will modify the worksheet and attach it. I thought this is a basic problem and there might be some simple fixes.

    Thank you very much,.
    newbie !

  2. #2
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: worksheet protect and macros

    Hi,

    Protecting a worksheet shouldn't make it inaccessible. What is the code you are using to protect the worksheet? It should be something like
    Please Login or Register  to view this content.
    Also, can you manually click on the worksheet to select it after your VBA has protected it?

    Hope this helps

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    46,697

    Re: worksheet protect and macros

    Two options: unprotect the sheet before you try to make changes and protect it again afterwards. Or, when you protect it, use:

    UserInterfaceOnly
    Optional
    Variant
    True to protect the user interface, but not macros. If this argument is omitted, protection applies both to macros and to the user interface.


    This option will need to be applied whenever the workbook is opened as the status is not retained.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: worksheet protect and macros

    Hi

    You could either include as the first line of your macro

    Sheets("Calc").Unprotect Password:= "your password"
    then as the last line
    Sheets("Calc").Protect Password:= "your password"

    Whilst that will work it's always advisable to use the sheet code names rather than the tab name, Tab names can be changed with obvious problems. So if for instance the Calc sheet is Sheet3 then used
    Sheet3.UnProtect
    etc..

    However have you considered hiding the Calc sheet and setting the Visible property of the Calc sheet to xlSheetVeryHidden leaving it unprotected. A casual user will not know it's there and won't see it in the Format Hide and Unhide area where you can usually see what sheets are hidden.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Registered User
    Join Date
    08-30-2013
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: worksheet protect and macros

    thanks for the quick reply. Every little thing helps.
    I am protecting it manually outside. I was not using vba code (like the one you show) to protect the sheet.

  6. #6
    Registered User
    Join Date
    08-30-2013
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: worksheet protect and macros

    Hi TMS,

    Sorry I don't understand how to use your suggestion (UserInterfaceOnly....)

    Here is the code within my command button. Only if I unprotect and leave it visible am I able to run without any errors. Even hiding this worksheet causes run time error 1004. What am I missing...

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    08-30-2013
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: worksheet protect and macros

    Quote Originally Posted by Richard Buttrey View Post
    Hi

    You could either include as the first line of your macro

    Sheets("Calc").Unprotect Password:= "your password"
    then as the last line
    Sheets("Calc").Protect Password:= "your password"

    Whilst that will work it's always advisable to use the sheet code names rather than the tab name, Tab names can be changed with obvious problems. So if for instance the Calc sheet is Sheet3 then used
    Sheet3.UnProtect
    etc..

    However have you considered hiding the Calc sheet and setting the Visible property of the Calc sheet to xlSheetVeryHidden leaving it unprotected. A casual user will not know it's there and won't see it in the Format Hide and Unhide area where you can usually see what sheets are hidden.
    I pasted the code from within the commandbutton. I am noticing that even hiding (without protecting) causes the run time error and compilation to stop at "Sheets("CALC").Select. Setting it to visible allows it to run.

    Thanks !

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    46,697

    Re: worksheet protect and macros

    You can't select a hidden worksheet.

    In most cases, you don't have to select an object to manipulate it. And, for improved performance, you should avoid it.

  9. #9
    Registered User
    Join Date
    08-30-2013
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: worksheet protect and macros

    Quote Originally Posted by TMShucks View Post
    You can't select a hidden worksheet.

    In most cases, you don't have to select an object to manipulate it. And, for improved performance, you should avoid it.
    I tried the following

    Please Login or Register  to view this content.
    But it does not seem to paste the contents on to the output sheet. However it doesnt give me run time error. I also tried Visible=False. Still does the same.

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    46,697

    Re: worksheet protect and macros

    Try (untested):

    Please Login or Register  to view this content.

    You'll need to adjust the
    Please Login or Register  to view this content.
    bit as the amended code doesn't select any cells so the ActiveCell might not be what it would have been.

    Regards, TMS

  11. #11
    Registered User
    Join Date
    08-30-2013
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: worksheet protect and macros

    Quote Originally Posted by TMShucks View Post
    Try (untested):

    Please Login or Register  to view this content.

    You'll need to adjust the
    Please Login or Register  to view this content.
    bit as the amended code doesn't select any cells so the ActiveCell might not be what it would have been.

    Regards, TMS
    Awesome. That worked ! Thanks a lot. As I understand now, "select" does not work with hidden sheets, so we need to club the code into one line.

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    46,697

    Re: worksheet protect and macros

    You're welcome. Thanks for the rep.


    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to protect worksheet containing macros - tried, but losing functionality
    By WillGe in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 05-04-2012, 02:45 AM
  2. [SOLVED] Protect worksheet that contains Macros
    By Bakar in forum Excel General
    Replies: 3
    Last Post: 04-17-2012, 12:33 PM
  3. My macros wont work when I protect my worksheet
    By MGK086 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-15-2009, 09:22 AM
  4. Protect macros in a worksheet
    By LAF in forum Excel General
    Replies: 3
    Last Post: 07-15-2009, 02:54 PM
  5. Replies: 2
    Last Post: 03-20-2009, 07:58 AM

Tags for this Thread

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