Must enter value (2003)

[verada]

Loungers,

I'm need to set up a rule (like in validation) where a value (numeric) must be entered into a cell, if a value in say A1 is greater than 0, then a value must be entered in say B1 - if not entered then a message appear detailing requirement - like in validation

I've tried data validation, but as I understand it this only related to the value entered in the cell related to the validation rule

Any thoughts?

[HansV]

Excel is not the ideal application for this kind of thing. If you really need this, use a database application such as Access.

You could use conditional formatting to draw attention to missing values (you can refer to other cells in the conditions for conditional formatting).

It might be possible to use the Worksheet_Change and Worksheet_SelectionChange events to try to enforce data entry, but it would be rather tricky.

[LegareColeman]

Validation probably will not work for what you want. What do you want to trigger the message appearing? When the user tries to close the workbook? When the user changes to a different worksheet?

[verada]

Thanks Hans - will look at other options

[verada]

Legare

Thanks for the reply - I was hoping that a message would appear after moving to the next cell if no data was entered - as with data validation

[LegareColeman]

Does the code below, placed in the module behind the worksheet do what you want?

<code>
Option Explicit
Dim oPrevCell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim dblVal As Double
If Not oPrevCell Is Nothing Then
If Not Intersect(oPrevCell, Range("B1")) Is Nothing Then
If Range("A1").Value <> "" Then
dblVal = 0
On Error Resume Next
dblVal = Range("A1").Value
If dblVal > 0 And Range("B1").Value = "" Then
MsgBox "Cell B1 must not be empty."
Application.EnableEvents = False
Range("B1").Select
Application.EnableEvents = True
Exit Sub
End If
End If
End If
End If
Set oPrevCell = Target
End Sub
</code>

[verada]

Legare

Sorry about the delay in replying - been away.

Thanks for the code will give it a go