Validation

R

rob nobel

I would like to use Data Validation to stop someone altering data. This I
can do BUT the validation does not prevent the person from deleting the
contents of the cell, only from altering it. Is there a way to stop people
deleting the contents of the cell (other than by locking the cell and
protecting the sheet)?
 
J

J.E. McGimpsey

One way:

In the Data Validation dialog, uncheck the "ignore blanks" checkbox
- this will prevent a blank from being accepted.

Note however, that Validation is vulnerable to the user pasting a
value into the cell - it removes validation from that cell entirely.
So your best bet might be training the users.

Alternatively, you could use a Worksheet_Change event macro to
prevent the cell from being left blank
 
J

Jim

AFAIK, there is no way to prevent alteration of a cell's contents except
where you do not allow cells to be selected via VBA. I agree with the
content of J.E.'s post, but he did not seem to address the question.
 
R

rob nobel

Thank you gentlemen!
I like the suggestion to....
"Alternatively, you could use a Worksheet_Change event macro to
prevent the cell from being left blank"
Can someone please give me this macro or at least start me off?
Rob
 
R

rob nobel

Sorry to have to ask this again, but I don't seem to have a reply. How does
one write a procedure to do....
"Alternatively, you could use a Worksheet_Change event macro to
prevent the cell from being left blank."
Rob
 
J

J.E. McGimpsey

Say your cell of interest was A1. Then, in your worksheet code
module, put something like:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
With Range("A1")
If IsEmpty(Target.Value) Then
MsgBox "You can't leave A1 empty!"
Application.EnableEvents = False
Range("A1").Value = gvOldA1
Application.EnableEvents = True
Else
'Validation code here
gvOldA1 = Range("A1").Value
End If
End With
End If
End Sub

put in your ThisWorkbok module:

Private Sub Workbook_Open()
gvOldA1 = Sheets("Sheet1").Range("A1").Value
End Sub

and this in a regular code module:

Public gvOldA1 As Variant
 
Top