VBA Excel - Mandatory Cell

[slickp007]

Excel 2003

What i'd like to do is, that if say cell A4 had any sort of text, number or date in it, then this then makes cell B4 mandatory. I've created some VBA that makes cell A4 mandatory:

Option Explicit
Dim Mandatory 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$4"
Set Mandatory = Target
Case Else
If Not Mandatory Is Nothing Then
If Mandatory = "" Then
Mandatory.Select
MsgBox "You cannot leave this cell blank"
End If
End If
End Select


End Sub


However i need some help with how to make it so that if there is anything in A4 then B4 is mandatory. Help?

 

[SkipVought]

Hi,

You have to think about what event should be the trigger for validating your data. SelectionChange is not a good one. The use ought to be able to wander around a do stuff on the sheet, unless you have prescribed a strict order of prescidence.

For instance, if the sheet were protected and the user is TABBING from one UNLOCKED cell to the next, and the rule is that you cannot LEAVE the cell until some value is entered in the cell, that can be done with a SIMPLE Data > Validation rule WITHOUT any VBA.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}