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?

VBA to allow date change in a cell, but not deletion of cell value.
- FrankM
- Thread is marked as Resolved.
-
-
-
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 ...?
-
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
CodePrivate 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
-
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 ... !!!
-
Thanks. At least I now know want I want is not possible.
-
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 ...
-
Thank You, for working out a solution. it is what I was looking for.
-
Quote
Thank You, for working out a solution. it is what I was looking for.
Thanks for your Thanks ... And for the Like ...:smile:
-
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 IfEnd 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 = TrueEnd 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:
-
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
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!