Mar 27 2018 09:45 PM
How can I prevent the RIGHT CLICK on a worksheet (to deactivate "Delete". "Rename", "move or Copy" commands?);
protect workbook - would not work as it "freezes" all worksheets.
I want to prevent a specific worksheet from being deleted, renamed, moved or copied (duplicated).
Pressing Ctrl key + mouse dragging of sheet tab to the right also copy sheet - this also has to be deactivated.
My workbook consists of several worksheets, there is a specific sheet that is the template and I don't want it to be duplicated within the workbook but still enabling users to enter data to it.
Thanks a lot
Mar 28 2018 05:29 PM
SolutionYou can block delete, copy, move of specific worksheet using VBA worksheet change event, however, the problem with macro based solution is, that If user does not enable macro, the protection is not enabled, and if workbook is opened with macros being disabled then worksheet isn't protected.
Lets assume you are protecting a specific worksheet called "Form" (as per your earlier examples) then placing this code into the worksheet will do the job.
If you download the attached workbook and enable macro. you cannot delete, move, copy the Form worksheet, while you can delete,move or copy other worksheets.
Private Sub Worksheet_Activate() ThisWorkbook.Protect "Hello" End Sub Private Sub Worksheet_Deactivate() ThisWorkbook.Unprotect "Hello" End Sub
Mar 28 2018 10:57 PM
Mr Jamil Mohammad
Thank you for your unwavering assistance
The file book1 you sent was perfect - it blocked the insert,delete,move or copy mode!!
but as you know my worksheet "Form" has already a macro in it
how can I insert your macro?
I copied it at the end THEN AT THE BEGINNING of the existing macro -- it doesn't work :(
you have my file "EUREKA Desking Program..." - would you please show me how it is inserted into my worksheet.
Many thanks again
Mar 30 2018 08:21 PM
Mar 30 2018 09:08 PM
Mr Jamil Mohammad
Thank you so much for your help
The file you send worked perfectly!
The "Right Click" for sheet (Form) is disabled!!
The blinking is finally operational without error messages coming out!!!
just one small thing - am curious what is "Auto_close, Auto_open and Restore"?
can I "unhide" them from the macro box? I want just the macros relevant to the workbook operation to be visible in the macro box.
MANY MANY MANY THANKS
Mar 30 2018 09:45 PM
Mar 30 2018 10:00 PM
Mr Jamil Mohammad
Thank you again (and again) for your unwavering reply
I am now testing my program with your macros (I hope no more error messages pop out!)
I will send you the final copy of it afterwards
I found so many solutions in the net - but your assistance is somewhat "close and personal" and I am very very much touched.
My eyes is now opened with the wonderful usages of VBA
I have learned many things - though not a lot - but very helpful tips to my workbook
My deepest gratitude to you and wishing you more power in all of your endeavors.
Mar 31 2018 07:25 AM
Mar 31 2018 08:11 AM
Mr Jamil Mohammad
after several trials I finally nailed it!!
it worked like a charm!
but when I close it without saving - error pops up! (see below image)
it took me a while to solve it --
when there is an open excel file alongside with the program - it goes awry
when I close the other file - it worked perfectly!
how do we remedy this?
so sorry to bother you again
Thank you as usual..
Mar 28 2018 05:29 PM
SolutionYou can block delete, copy, move of specific worksheet using VBA worksheet change event, however, the problem with macro based solution is, that If user does not enable macro, the protection is not enabled, and if workbook is opened with macros being disabled then worksheet isn't protected.
Lets assume you are protecting a specific worksheet called "Form" (as per your earlier examples) then placing this code into the worksheet will do the job.
If you download the attached workbook and enable macro. you cannot delete, move, copy the Form worksheet, while you can delete,move or copy other worksheets.
Private Sub Worksheet_Activate() ThisWorkbook.Protect "Hello" End Sub Private Sub Worksheet_Deactivate() ThisWorkbook.Unprotect "Hello" End Sub