Unprotect sheet everytime to enter or delete data and that is undesirable

Question

In the attached macro below data validation is possible.

Problems :

The request for validation message appears as soon as i clic in any cell to enter data whether the cell is empty or not.
Moreover, I've to unprotect the sheet to enter any data and this happens as soon as i change cells.

I've tried deleting lines of codes but have ended up not giving the desired results and could hardly get anything online in this regard.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    Dim k As Integer
    Dim confirm As Integer
    Dim Cell As Range
    If Target.Column = 2 Or Target.Column = 3 Or Target.Column = 4 Or Target.Column = 5 Or Target.Column = 6 Or Target.Column = 7 Then
        confirm = MsgBox("Souhaiteriez vous confirmer la saisie de cette donnée?" _
                & vbCrLf & "Vous ne serez pas autoriser à la changer!", vbYesNo, "confirm Entry")
        Select Case confirm
        Case vbYes
            With Me
                .Unprotect
                .Cells.Locked = False
                For k = 7 To 102
                    If .Cells(k, 6).Value <> "" Then
                        .Cells(k, 2).Value = Date
                        .Cells(k, 2).NumberFormat = "m/d/yyyy"
                    End If
                Next
                .Cells.Locked = True
                .Protect Password:="10"
            End With
        End Select
    End If
    Application.EnableEvents = True
End Sub

Request :

That the request for validation message appears only when i have enetered data in the cell and not before.
To delete anything a password is needed.
That the need to unprotect the sheet in order to enter any information should not be the case.

i will be available for any clarifications.

Thank you to all in advance for your help.

I don't think you can achieve your 2nd Point. For 1st add `If Target.Value = "" Then Exit Sub` at the top of your code. For 3rd comment out both the lines with `.Protect` & `.Cells.Locked` — Mikku, Aug 05 '19 at 14:47
What you can do is, lock all the filled cells, and unlock all the Blank cells, So that anyone can fill them without password, But to change the already filled cells you will need a password. — Mikku, Aug 05 '19 at 14:51

score 0 · Answer 1 · answered Aug 05 '19 at 15:45

Below are the list of solutions for your issues

Instead of Private Sub Worksheet_SelectionChange(ByVal Target As Range) you should be using Private Sub Worksheet_Change(ByVal Target As Range). Link: https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.change
To allow VBA to execute a code even if the cell/sheet is protected, you need to use the UserInterfaceOnly = True method while protecting the sheet using VBA. Please check out the following Question & Answer: https://stackoverflow.com/a/126032/6908282

Note: The sheet needs to be always protected using the UserInterfaceOnly= True method for VBA to be able to execute a code. So its recommended to add this in Workbook_Open() method

Unprotect sheet everytime to enter or delete data and that is undesirable

1 Answers1