Results 1 to 3 of 3

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

  1. #1

    Thread Starter
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,376

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

    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:

    Code:
    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

  2. #2

    Thread Starter
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,376

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

    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.

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,325

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

    Quote Originally Posted by jdc2000 View Post
    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: "?!?!?!?!?????"
    Last edited by Zvoni; Aug 31st, 2023 at 01:58 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width