Forum Discussion
prevent copy sheet within a workbook for a specific worksheet (deactivate RIGHT CLICK?)
- Mar 29, 2018
You 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
You 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
- Lorenzo KimMar 29, 2018Bronze Contributor
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
- JamilMar 31, 2018Bronze ContributorHi, I sent you the file with the more changes that you asked in the VBA. please confirm.
- Lorenzo KimMar 31, 2018Bronze Contributor
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