Forum Discussion

Viorica000's avatar
Viorica000
Copper Contributor
May 02, 2023
Solved

Unprotect doesn't work with VBA code

Hello everyone, I have the following code which that does not allow the user to paste values in a Data Validation List Column.  My problem is that I have columns (without data validation) that have...
excel
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    May 03, 2023

    Viorica000 

    Thanks, that is very useful. I see what happens:

    When the sheet is protected, Excel only copies/pastes the value, not the cell's formatting (including the data validation rule). So xCheck2 will always be the same as xCheck1.

    On the plus side, the data validation of the target cell will not be overwritten.

    But the downside is that you can paste a value that is not part of the data validation list (if any).

    I don't see a solution, except altogether forbidding to paste to a cell with a data validation drop down:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xCheck1 As Boolean
    If Target.Count > 1 Then
        Exit Sub
    End If
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    On Error Resume Next
    xCheck1 = Target.Validation.InCellDropdown
    On Error GoTo 0
    If xCheck1 Then
        MsgBox "It is not allowed to paste!"
        Application.Undo
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

     

TragicReindeer's avatar
TragicReindeer
Copper Contributor
May 02, 2023

Viorica000  I didn't have any trouble using the code you inserted in your post.  I don't fully understand what the problem is, perhaps you have more code to share?  You mentioned that your main issue is with protecting and unprotecting, so any relevant protection code would help us to identify the issue.

 

  • Viorica000's avatar
    Viorica000
    Copper Contributor
    May 02, 2023

    Hi TragicReindeer  thanks for the answer
    Yes, ma case is:
    Have 3 column, where column A and B have "Format cells.."-->"Protection" -->"Locked"
    Column C doesn't have Locked option because is my Data Validation List. 

    When i Protect my sheet, the code VBA doesn't work anymore, if i remove the protection the vba works. 

    So if the sheet is protected and I paste, I don’t get the error message.

    thank you very much

     

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      May 02, 2023

      Viorica000 

      You might unprotect/reprotect the sheet in the code. Replace "secret" with the password you used ("" if none).

       

      Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xValue As String
    Dim xCheck1 As String
    Dim xCheck2 As String

    If Target.Count > 1 Then
        Exit Sub
    End If

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Me.Unprotect Password:="secret"

    xValue = Target.Value
    On Error Resume Next
    xCheck1 = Target.Validation.InCellDropdown
    On Error GoTo 0
    Application.Undo
    On Error Resume Next
    xCheck2 = Target.Validation.InCellDropdown
    On Error GoTo 0
    If xCheck1 = xCheck2 Then
        Target = xValue
    Else
        MsgBox "It is not allowed to paste!"
    End If

    Me.Protect Password:="secret"
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
      • Viorica000's avatar
        Viorica000
        Copper Contributor
        May 03, 2023

        Hi, HansVogelaar thanks for the answer

         

        Unfortunately it does not work, the sheet is protected and when try to copy paste the value is pasted without giving me the alert.

         

        So I think it’s the Unprotect function that doesn’t work, because when I manually remove the protection of sheet and I try to copy paste, I get out the alert and the sheet protected automatically.

         

        thank you very much

        Have a good day 

Resources