Mandatory input in cells -?
Posted by Christy on February 15, 2002 2:02 PM
Hi,
Is there a way to make it mandatory that a cell be filled in?
This cell needs to contain a date, I would like it to verify & require one, either before tabbing out of the cell or before printing, whichever is easier. Validation does not work because if you just tab out of the cell, validation doesn't care.
Thanks so much for your help!!
~Christy
Posted by John Bennito on February 15, 2002 2:34 PM
Use the Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
and put in code to verify what was keyed in the cell. You will have to store the previous cell address in a spot on the worksheet. when the selection change process is avtivated - check what the previous address was and what are the contents of the cell at that address. If it does not meet your criteria then take the user back to the cell and possible provide a message telling them what they have to do.
Hope this helps.
Posted by Beginner Bob on February 15, 2002 3:53 PM
Christy, try throwing this in the worksheet module, I messed around with it, and it worked. I'm sure there's a shorter way to do this, but I'm not sure what that might be. This should work for the exit cell approach, it would be similar for the Before_Print approach too. Change B3 to the cell you want.:
--------------------
Public OLDCELL As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If OLDCELL = "$B$3" Then
If Target.Address = "$B$3" Then Exit Sub
If IsDate(Range("B3").Value) Then Exit Sub
MsgBox "PLEASE ENTER A DATE IN CELL B3."
With Range("B3")
.ClearContents
.Select
End With
Exit Sub
End If
OLDCELL = Target.Address
End Sub
---------------------------
Beginner Bob
