VBA to add Data Validation fails when list source sheet is protected

[jdc2000]

I have a workbook that has a Sheet1 where a user can enter data into cells and a Sheet3 that has a list of various table entries that are used to create validation lists for some of the cells on Sheet1. I dynamically change some of the validation lists on a cell in Sheet1 based on what is entered in a different cell on Sheet1. I also have some code to reset certain Sheet1 cells values or validation lists when the workbook is first opened, in case it was saved with previous data.

The code to reset one of the validation lists works OK if the Sheet3 source data sheet is not protected, however, if I protect that sheet, I get a 1004 error when I try to reset the validation list on the
unprotected cell on Sheet1 to the range of values on Sheet3. Since I am not changing anything on any protected cells, I would thing that the code should work.

Any ideas as to why this is happening, and what I can do to eliminate the problem would be appreciated.

Code to reset the Sheet1 cell validation list from the SHeet3 data:

Option Explicit


Private Sub Workbook_Open()

    Sheet1.Cells(2, 2) = Date
    If Sheet1.Cells(12, 2) = "" Then Sheet1.Cells(12, 3) = ""
    
    With Sheet1.Range("B7").Validation
        .Delete ' Delete previous validation (this works whether the Sheet3 data is protected or not!)
        .Add Type:=xlValidateList, _ ' This fails if Sheet3 is protected
             Formula1:="=Tables!$A$2:$A$15"
    End With
    
    With Sheet1.Range("B8").Validation
        .Delete ' Delete previous validation
    End With
    
    With Sheet1.Range("B12").Validation
        .Delete ' Delete previous validation
    End With

End Sub

[jdc2000]

After some additional research, it appears that you cannot update the Data Validation on an unprotected cell with the worksheet protected, even though you can remove the Data Validation. The only workaround that I have seen is to unprotect the sheet, change the Data Validation, and then re-protect the sheet, however, this leaves a rather large security hole, since the password is required and anyone can then discover what it is and unprotect the sheet.

If anyone has any other ideas on how to solve this issue, suggestions would be welcome.

[Zvoni]

After some additional research, it appears that you cannot update the Data Validation on an unprotected cell with the worksheet protected, even though you can remove the Data Validation. The only workaround that I have seen is to unprotect the sheet, change the Data Validation, and then re-protect the sheet, however, this leaves a rather large security hole, since the password is required and anyone can then discover what it is and unprotect the sheet.

If anyone has any other ideas on how to solve this issue, suggestions would be welcome.

Protect your VBA-Project with a Password

Independent of this, i've done away with Sheet-Protections in general, since it doesn't take me a minute to throw out any sheet-protections there might be anywhere in a workbook, without knowing the password.

EDIT: I remember the owner (!! --> The man who pays my salary) of the company i work for (600+ employees) being on a visit to our branch, and he and my general manager were working on an Excel-File a client sent him, but it had sheet-protection in a way, that, basically, the Workbook was "unworkable".
My GM told him, they should ask me, if i had an idea.
Me (starting a Timer)
Me (doing my black magic)
Me (stopping Timer)
Me: "Eh? 48 seconds? I'm getting slow in my old age...."
My GM and the Boss: "?!?!?!?!?????"