VBA to allow date change in a cell, but not deletion of cell value.

  • I am trying to only allow date modification in cell G19. Currently I have Data Validation on the cell which only allows date formats. However a user can delete the date, which prompts the user to enter a date, after the deletion, has occurred. I would prefer to have a error message pop up, prior to the deletion. I cannot protect the sheet, because it causes problems with other cells. Is it possible to just allow date modification?

  • Hello,


    Not sure to understand your problem ...


    If your Date Validation is working fine ... you could use the Input Message as a Warning ...


    Could that help ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Validation is working fine. I do have an active input message. However a certain user has a history of ignoring the warnings, I am trying to have a warning come up prior to the deletion. or possibly some code that would UNDO the deletion after the change event. BUT only a deletion.

  • OK ...


    Are you thinking about preventing the Delete from operating ...?


    For example, when selecting the cell G9 ... the Delete and Backspace keys could be disabled ...


    You could test in the respective Sheet Module


    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Target.Address = "$G$9" Then
        Application.OnKey "{DEL}", ""
        Application.OnKey "{BACKSPACE}", ""
      Else
        Application.OnKey "{DEL}"
        Application.OnKey "{BACKSPACE}"
      End If
    End Sub


    Hope this can help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • The code does prevent deletion, but does not allow modification. I.E. from 10/1/2000 to 10/1/2019. Also would it be possible to include an error message, when a deletion is attempted?

  • Sorry but the Event macro does not interfere at all with any type of modification ... !!!

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Thanks. At least I now know want I want is not possible.


    Not really ... :wink:


    See attached your Test file with the proposed macro ...


    and let me know if it is in line with your expectations ... or not ...

  • Quote

    Thank You, for working out a solution. it is what I was looking for.


    Thanks for your Thanks ... And for the Like ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Something weird is happening. Your test sample works as expected. However when I run it in my file, I get the following error message: "Cannot run the macro"file location& name!. The macro may not be available in this workbook or all macros may be disabled." This is a macro enabled workbook, & the other macros work. Below is a listing of the code on the sheet in question.
    [VBA]
    Option Explicit
    'Public Sub AllowMacros()
    'This allows a macro to run & keep the sheet protected.
    'Me.Protect UserInterfaceOnly:=True
    'End Sub
    Private Sub Worksheet_Change(ByVal Target As Range)
    'This section of code clears the second listing of a duplicate, & allows the first.
    Dim r As Long
    If Not Intersect(Range("B5:B17,H4:H16"), Target) Is Nothing Then
    Application.EnableEvents = False
    For r = 5 To 17
    If Range("H" & r - 1).Value = "No" And Range("B" & r).Value <> "" Then
    Range("B" & r).ClearContents
    End If
    Next r
    Application.EnableEvents = True
    End If


    'This section of code provides notification if there is a scheduling conflict.
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("H5:H10, H13:H17")) Is Nothing Then
    If LCase(Target.Value) = LCase("No") Then
    MsgBox "If there is pink in a previously filled cell, after checking No." & vbCrLf & _
    "There is Scheduling conflict." & vbCrLf & _
    "Choose another time or reschedule the first veteran" & vbCrLf & _
    "Remember! New Career Link Visits require 1 hour.", vbCritical, "Vocational Services - OVR " & ActiveSheet.Name
    End If


    End If
    'This code prevents modification or deletion of cells in the range below. You do not need to protect the sheet.
    If Intersect(Target, Range("A3:P3,B11:P12,B33:L50,B59:B60,C32:J32,D4:D17,E20:F21,F59:N60,G20:G22,H18:I18")) Is Nothing Then Exit Sub
    On Error GoTo ExitPoint
    Application.EnableEvents = False
    If Not IsDate(Target(1)) Then
    Application.Undo
    MsgBox " You can't delete or modify cell contents in this range " _
    , vbCritical, "Vocational Services - OVR " & ActiveSheet.Name
    End If
    ExitPoint:
    Application.EnableEvents = True


    End Sub


    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$G$19" Then
    Application.OnKey "{DEL}", "Warning"
    Application.OnKey "{BACKSPACE}", "Warning"
    Application.OnKey " ", "Warning"
    Else
    Application.OnKey "{DEL}"
    Application.OnKey "{BACKSPACE}"
    Application.OnKey " "
    End If
    End Sub
    [/VBA]
    I can't see anything wrong. Is there something I am missing?

  • I am SOOOOOOOOOOO sorry. I forgot to add this to the module:
    [VBA]Private Sub Warning()
    MsgBox " You can Edit the Date ...or..." & vbNewLine _
    & vbNewLine & " You can Type a New Date on top of the existing one ... " & vbNewLine _
    & vbNewLine & " BUT you cannot Delete the cell's content ....!!!"
    End Sub
    [/VBA]
    It works fine. Apparently it is too early for me to think.

  • Glad you could solve the problem ... :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • I added this piece of code to get rid of the Microsoft title in the window.[VBA], vbInformation, "Vocational Services - OVR " & ActiveSheet.Name[/VBA]
    Again Thank You

  • You are welcome

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!