Forum Discussion

Lorenzo Kim's avatar
Lorenzo Kim
Bronze Contributor
Mar 28, 2018
Solved

prevent copy sheet within a workbook for a specific worksheet (deactivate RIGHT CLICK?)

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 ...
  • Jamil's avatar
    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
    
    

     

     

     

     

Resources