Required entries

patricknoreene · Apr 17th, 2002, 06:29 AM

I am trying to figure out how to make users enter data in certain fields before they can move to next entry. My Comments section must have data. I tried to use data validation, but it only works if you actually enter data and then it is checked to see if the data is valid. But, Excel will allow you to enter thru that section without stopping.
How can I get Excel to not allow the user to go any further until they have entered data in this field (Comment)?

lenze · Apr 17th, 2002, 07:35 AM

This code will require that a value be entered in a cell. Substitute the correct cell addresses.

Option Explicit
Dim Required As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub
Select Case Target.Address
Case "$A$5", "$A$10", "$A$15", "$A$20", "$A$25", "$A$30"
Set Required = Target
Case Else
If Not Required Is Nothing Then
If Required = "" Then
Required.Select
MsgBox "You cannot leave this cell blank"
End If
End If
End Select

End Sub

Place this code in the Sheet module

[ This Message was edited by: lenze on 2002-04-17 06:36 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 17th, 2002, 06:29 AM	#1
patricknoreene New Member Join Date: Apr 2002 Posts: 1	I am trying to figure out how to make users enter data in certain fields before they can move to next entry. My Comments section must have data. I tried to use data validation, but it only works if you actually enter data and then it is checked to see if the data is valid. But, Excel will allow you to enter thru that section without stopping. How can I get Excel to not allow the user to go any further until they have entered data in this field (Comment)?

Apr 17th, 2002, 07:35 AM	#2
lenze MrExcel MVP Join Date: Feb 2002 Location: Helena, MT Posts: 13,690	This code will require that a value be entered in a cell. Substitute the correct cell addresses. Option Explicit Dim Required As Range Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If Target.Cells.Count > 1 Then Exit Sub Select Case Target.Address Case "$A$5", "$A$10", "$A$15", "$A$20", "$A$25", "$A$30" Set Required = Target Case Else If Not Required Is Nothing Then If Required = "" Then Required.Select MsgBox "You cannot leave this cell blank" End If End If End Select End Sub Place this code in the Sheet module [ This Message was edited by: lenze on 2002-04-17 06:36 ]