How to allow VBA-based hide/unhide of worksheets to continue working while the whole workbook is protected?

[Rnkhch]

Hello,

I wrote the following code to automatically hide/unhide the relevant worksheets based on a dropdown menu selection. However, I also want to protect the workbook so that my "Control" worksheet (not in the code) can never be unhidden and also so that no users can delete any of the worksheets.

Private Sub Worksheet_Change(ByVal Target As Range)
If ['Info-Setup'!$J$10] = "Template style" Then
Sheets("Input").Visible = True
Sheets("Plates-Input").Visible = False
Sheets("Plates-Data").Visible = False
Sheets("Validity").Visible = True
Sheets("Excel").Visible = True
ElseIf ['Info-Setup'!$J$10] = "Plate style" Then
Sheets("Input").Visible = False
Sheets("Plates-Input").Visible = True
Sheets("Plates-Data").Visible = True
Sheets("Validity").Visible = True
Sheets("Excel").Visible = True
ElseIf ['Info-Setup'!$J$10] = "" Then
Sheets("Input").Visible = False
Sheets("Plates-Input").Visible = False
Sheets("Plates-Data").Visible = False
Sheets("Validity").Visible = False
Sheets("Excel").Visible = False
End If
End Sub



After I locked the workbook, unfortunately my dropdown menu stopped working; upon selecting any of the dropdown options, I got the debug message.

Is there any way to deal with this situation?

 

[Rnkhch]

I found a partial workaround for this situation (without enabling "protect workbook"):

Part 1) I found code online that prevents deleting of worksheets

2 Ways to Prevent Excel Sheet from Being Deleted | Password Recovery

This tutorial shows you 2 ways to protect a particular or all sheets from being deleted in Excel 2016/2013/2010/2007.

www.top-password.com

Part 2) To prevent unhiding of my Control worksheet, I set its visible property to "very hidden"

Part 3) I put a password for code editing, so nobody can open the editor and reverse my above-mentioned protections.

However, I have so far not been able to find any working code that would prevent renaming of worksheets. I tried these two suggestions, but neither worked for me (perhaps they were good for older versions of Excel?):

How to prevent from changing sheet name in Excel?

ExtendOffice provides a comprehensive range of professional software solutions, enhancing productivity and efficiency for businesses worldwide.

www.extendoffice.com

Home - Algorithms

Learn and solve top companies interview problems on data structures and algorithms

excel-macro.tutorialhorizon.com

The other issue would be to prevent moving the worksheets around, which I wasn't able to find good code for either, although I'm not particularly concerned about moving of worksheets as this will not effect their functionality (but it might make it a bit less intuitive for some users to find their way around the worksheets).

Not sure if there are any other aspects to "protect workbook" that I should be thinking about (besides "deleting", "unhiding", "renaming", and "moving" of worksheets)?