1

I have protected my worksheet during workbook_open() event. Using Worksheet_SelectionChange, when user selects a cell, a popup date picker appears to capture date from the user and enter it into that respective protected cell.

My requirement is, I want to provide access to users where they can delete the date / content inside the cell but not make to edit it. I tried using worksheet.Protect methods. But they didn't suit my particular need.

Please help. The code i used,


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Set wb = ThisWorkbook
Set ws0 = wb.Worksheets("DB")
Set ws1 = wb.Worksheets("Design-HW")

rowposition = 4

productno = ws1.Cells(Rows.Count, ws0.Range("A4").Value).End(xlUp).Row 'to find the number of rows in product no column
Set datesrange = ws1.Range(ws1.Cells(rowposition + 2, ws0.Range("H4").Value), ws1.Cells(productno, ws0.Range("P4").Value + 1))

      If Not Application.Intersect(datesrange, Target) Is Nothing Then
        Call ShowUserform 'Calls popup date picker and the date user selects is entered in the ws0.Range("H1")
        If ws0.Range("H1").Value <> "" Or ws0.Range("H1").Value <> Empty Then
        Application.EnableEvents = False
        ws1.Unprotect Password:="1234"
        ws1.Cells(Target.Row, Target.Column).Locked = False
        ws1.Cells(Target.Row, Target.Column).Value = ws0.Range("H1").Value
        ws1.Cells(Target.Row, Target.Column).Locked = True
        ws1.Protect Password:="1234", UserInterfaceOnly:=True, AllowFiltering:=True, AllowFormattingCells:=True, _
AllowDeletingRows:=True
        ws0.Range("H1").Value = Empty
        Application.EnableEvents = True
'        GoTo exitingdate:
        End If
        End If

End Sub
Sandy
  • 87
  • 10
  • try use worksheet change event, add variable with previous value off cell, add condition of cell adress and condition to check is new value = "" and should work – Tomasz Feb 16 '21 at 06:46
  • How long will the user not be able to change the date after it has been deleted? Keep in mind that he might have accidentally picked the wrong one and deleted it. – VBasic2008 Feb 16 '21 at 06:47
  • @Tomasz as i have protected the worksheet when the user presses the delete key it throws the error that the cell is protected. So comparison is not possible – Sandy Feb 16 '21 at 06:56
  • @VBasic2008 thanks once again for always helping me. The issue is that the user cannot clear the contents of the protected cell. I want him/her to clear the content but not to edit it – Sandy Feb 16 '21 at 06:58
  • You can track keydown event and unlock sheet like here https://stackoverflow.com/questions/11153995/is-there-any-event-that-fires-when-keys-are-pressed-when-editing-a-cell – Tomasz Feb 16 '21 at 07:06

0 Answers0