Prevent Move or Copy of a sheet

[ABabeNChrist]

Is it possible to prevent a sheet from being moved or copied when selected?

move and copy.JPG

I understand I can Protect Workbook, but will this not cause a problem with hidden sheets when using VBA to hide and unhide.
I tried using "ActiveWorkBook.Unprotect "" and ActiveWorkbook.Protect structure:=True, Windows:=False, Password:=""

[Jan Karel Pieterse]

Check out the UserInterfaceOnly argument of the protect method.
It will allow you to modify what you need through code, but the user is still "protected".
You need to re-apply the protection with this argument set to true each time the workbook is opened, as it looses that state when it is closed.
So you need some code that reprotects your workbook using that argument set to true everytime the workbook is opened.

[Rudi]

I think that ABabeNChrist is asking if one can prevent a user from moving or copying a sheet. In other words to have the move/copy option greyed out in the context menu and the ribbon.

To prevent the sheets from being moved and copied you could use this code:
ActiveWorkbook.Protect Structure:=True

[HansV]

To disable the Move or Copy item, you must protect the workbook. If you then want to hide or unhide a worksheet, you must temporarily unprotect the workbook first, and reprotect it afterwards.
The Protect method for workbooks does not have a UserInterfaceOnly argument, unlike the one for worksheets.

[Jan Karel Pieterse]

Hi Hans,

Good catch. I was not entirely sure when I wrote my reply, and did not take the time to double-check.

[ABabeNChrist]

Thank you everyone for your assistance, most greatly appreciated
So basically I will need to use something like this to open and close each sheet, if I wish to disable the Move or Copy. I select my sheets from a userform

Code: Select all

  ActiveWorkbook.Unprotect
   ActiveSheet.Range("AM5").Select
     Worksheets("Grounds").Visible = xlSheetHidden
  ActiveWorkbook.Protect Structure:=True
     UserForm8.Show

[HansV]

Yes, that's the idea.

[ABabeNChrist]

Now If I wanted to password protect the Workbook, Is this the correct line of code, it doesnt seem to work

Code: Select all

    ActiveWorkbook.Unprotect Password:="Password Here"

Code: Select all

ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:=”Password Here”

[HansV]

If you look closely, you'll see that the second line has curly quotes around the password: ”Password Here”. These are not valid in VBA.
You should use straight quotes instead: "Password Here".