+ Reply to Thread
Results 1 to 5 of 5

Macro to protect only one sheet

  1. #1
    Registered User
    Join Date
    08-05-2011
    Location
    Kaavi
    MS-Off Ver
    Excel 2007
    Posts
    45

    Macro to protect only one sheet

    Hello,

    I have created memo that has one sheet called "Memo", it contains activex-objects, textboxes and drop down boxes. One object is called "Add new sheet", when I run this macro, it opens new sheet, a copy of this sheet "Memo".

    What I need is a code, that prevents user to select this "Memo"-sheet. I've tried to hide it but then, if I try to add new sheet, macro won't work, new sheet comes also hidden and whole book "breaks apart".

    "Memo"-sheet doesn't need to be hidden, I just want to protect it that user cannot do ANYTHING into it. I've also tried to protect whole workbook, but it won't help 'cuz activex-objects won't lock, I checked at object-properties and all them are "Locked", but still able for user to select.

    And one last question. Because of that these new sheets are copies of this sheet *Memo", and if this "Memo" is fully protected and THEN copied, these new copied sheets become also protected (??) What can I do that this "Memo" is protected, and when I copy it, new sheet comes UNprotected?? Anyone understand what I'm talking about.... ??

    Help me please!
    Last edited by Cre; 08-17-2011 at 06:51 AM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Macro to protect only one sheet

    You can't hide the sheet if it is the only one in the workbook.

    Add this line to your code after the sheet has copied

    Please Login or Register  to view this content.
    If you have a password then

    Please Login or Register  to view this content.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    08-05-2011
    Location
    Kaavi
    MS-Off Ver
    Excel 2007
    Posts
    45

    Re: Macro to protect only one sheet

    I know, it's not the only one in the workbook, there are several sheets and this "Memo" is the only one I wanna protect.

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Macro to protect only one sheet

    Quote Originally Posted by Cre View Post
    Hello,

    I have created memo that has one sheet called "Memo", it contains activex-objects, textboxes and drop down boxes. One object is called "Add new sheet", when I run this macro, it opens new sheet, a copy of this sheet "Memo".
    My suggested code will unprotect the new sheet

  5. #5
    Registered User
    Join Date
    08-05-2011
    Location
    Kaavi
    MS-Off Ver
    Excel 2007
    Posts
    45

    Re: Macro to protect only one sheet

    Ok, thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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