Forcing Data Entry in Certain Fields at Certain Times

[david@montanaro]

I am creating a form that could have either a single or multipl
instance of data entered (it is arranged so each unique instance woul
be a column in an Excel worksheet).

What I would like to do is to do is force the user to enter data in Al
Fields for a particular instance. Forcing the data entry in all field
would have to be conditional on checking a cell (cell or cells) fo
data. For example is there is an entry in the name cell of column
then all fields in column B require data entry.

It would also be great if not only could I force data entry or post
message if a fied is not filed out, but have the warning messag
indicate what fieds need to be filed in.

Any ideas would be welcome.

Thanks,
David Montanar

 

[Dick Kusleika]

David

What kind of form? A userform or a spreadsheet formatted to look like a
form?

 

[Arris]

Hi,
Well depending on when you want to test for the cells being filled i
and if your doing validation on excel data then you could use th
following method to check that if any data is entered in any cell i
the first 5 columns of row 1, it will force entry in the other
cells.
Currently this is set up for Rows 1 to 10.
Unfortunately this solution (after I coded it in a stand alon
workbook) didn't work when I embedded it as an OLE object within
Lotus Notes form. The Workbook_BeforeSave is never triggered in m
situation. But maybe it can be of use to someone else even if it is no
David. Also someone who has a better understanding of VBA should b
able to tidy this up a bit and display a msgbox detailing which cel
has to be filled along side scrolling to the cell,

Hope this is of some help, you should be able to alter it to work fo
columns rather than rows. Note this only validates the input when th
user tries to save the spreadsheet which is maybe not what you want t
accomplish. I'm still looking into alternative methods for forcin
mandatory field entry if any other field in the row has been entered
If I come up with another solution I'll post back.


Code
-------------------

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Set ws = Application.ActiveSheet
WithData = 0
ColumnSearch = 5
For RowCount = 1 To 10
For Each c In ws.Range(Cells(RowCount, 1), Cells(RowCount, ColumnSearch))
'Check all mandatory fields for this row have been completed
If c.Value <> "" Then
WithData = WithData + 1
End If
Next c
If WithData < ColumnSearch And WithData <> 0 Then
'Set Focus to problem cell and display error message telling user that they
'must enter a value
MsgBox "Please enter values in row " + CStr(RowCount), vbOKOnly, "Fill in Mandatory cell value"
Application.Goto Reference:=Cells(RowCount, 1), Scroll:=True
Cancel = True
Exit Sub
End If
WithData = 0
Next RowCount
End Sub

 

[Arris]

Because I'm limited to worksheet events in the embedded environment her
is a reworked version that informs the user that they must fill in th
first 3 cells of a row if they fill in any of the values. This i
triggered when the user moves to a new row.


Code
-------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If PreviousRowRef = 0 Then
PreviousRowRef = Target.Row
End If

RowRef = Target.Row
ColumnRef = Target.Column

If PreviousRowRef <> Target.Row Then
Set ws = Application.ActiveSheet
WithData = 0
FirstColumn = 1
LastColumn = 3 'Number of columns to check
For Each c In ws.Range(Cells(PreviousRowRef, FirstColumn), Cells(PreviousRowRef, LastColumn))
'Check all mandatory fields for this row have been completed
If c.Value <> "" Then
WithData = WithData + 1
End If
Next c
If WithData < LastColumn And WithData <> 0 Then
MsgBox "Please enter values in Row " + CStr(PreviousRowRef) + ", Cells " + CStr(FirstColumn) + " to " + CStr(LastColumn), vbOKOnly, "Fill in Mandatory cell value"
End If
End If
PreviousRowRef = Target.Row
End Sub