Validation

  • Thread starter Thread starter rob nobel
  • Start date Start date
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)?
 
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
 
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.
 
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
 
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
 
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
 
Back
Top