Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Prevent user from making a copy of a sheet.

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Las Vegas Nevada USA
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Good morning all.
    Any way to prevent a user from making a copy of a sheet in the same workbook??

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Protecting the workbook (Tools- Protection- Protect Workbook, give password if you want), will prevent them from adding any more sheets to the workbook.

    Hope this is what you meant

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Las Vegas Nevada USA
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not exactly. I want to prevent copies of specific sheets and allow others.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hmm, the only way I could think of is to add the following code to each of the worksheets you don't want to be copied. It's not very subtle and I'm sure there's a better way of doing it (you can get round the protection by selecting more than one sheet and copying them)-

    Private Sub Worksheet_Activate()

    ThisWorkbook.Protect "MyPassword"

    End Sub

    Private Sub Worksheet_Deactivate()

    ThisWorkbook.Unprotect "MyPassword"

    End Sub

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi ==

    many different options open all have dangers to be got enrn about ands so still copy.

    Only real fix i know is:

    If people need access your a bit stuck as all can be got around by advanced user.

    All that i have found is what i call real lock down,

    Auto_Open and remove all the toolbar ie that one that carries copy.

    Also the right click copy command need disable. and other bits, so not so simple.

    Also i tried to stop copy and name other than the original sheets name so will not acheive anything.

    All in all not very satisfactry. But BTW can still send to A and play at home????

    HTH


    Also before i forget what ever is done in Auto_Open needs to be reversed in Auto_Close else excel might just be that way for lief!


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  6. #6
    Guest

    Default

    Thanks for your input Mudface and Jack.
    I haven't tried your solution Jack but Mudface, your's does work. You are correct, however, about end running around it by grouping two sheets and making copys of both. This works as long as the first sheet in the group is not protected. Must be a better way.

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
  •