Forcing Data Entry in Certain Fields at Certain Times
 

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

-------------------


--
Message posted from http://www.ExcelForum.com

Forcing Data Entry in Certain Fields at Certain Times
 

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

-------------------


--
Message posted from http://www.ExcelForum.com