Two cells must have entries

**gteachman** · 05-18-2011, 05:01 PM

Hello,

First I want to thank cutter and mrice for helping me with a previous custom data validation challenge. Thanks.

Now I have another validation challenge. I have a form with locked and unlocked cells on a protected sheet and a protected workbook. This means that usually tabbing will take the user from one unlocked cell to the next unlocked cell.

Okay, I have two cells that both must either be blank or both have some entry. I would like to alert the user after leaving the second cell (entering some piece of data before they leave the cell) if the first cell is empty and not allowing the user to continue. Such as:

C1 has a data validation of whole number between 10 and 40.
D1 has a data validation of whole number between 100 and 400.

The user finds themselves in C1 and without entering any data tabs to D1 where they input the value of 200. Now, when the user tries to leave the D1, by any method, the validation needs to check if any data is in D1 and if there is checks to see if there is any data in C1. If there is data in both then the focus can move to the next cell, whatever that might be. However, if there is no data in C1 I want an alert telling the user that C1 is blank and needs to have something entered into it.

Does this make sense? I hope so, because it is driving me nuts.

Thanks for your help.

George

**Roger Govier** · 05-18-2011, 05:35 PM

Hi

You will need some event code on your sheet


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myrange As Range
Set myrange = Range("C1:D1")

If Not Intersect(Target, myrange) Is Nothing Then
Application.EnableEvents = False
If Target <> "" And Range("C1") = "" Then
Call MsgBox("You have not entered anything in C1." _
            & vbCrLf & "Please make an entry now, or delete the value in D1" _
            , vbExclamation, Application.Name)
            
Range("C1").Activate
ElseIf Target = "" And Range("C1") <> "" Then
Call MsgBox("You have not entered anything in D1." _
            & vbCrLf & "Please make an entry now, or delete the value in C1" _
            , vbExclamation, Application.Name)
Range("D1").Activate

End If
End If
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myrange As Range
Set myrange = Range("E1")  ' change to suit

If Not Intersect(Target, myrange) Is Nothing Then
Application.EnableEvents = False
If Range("D1") = "" And Range("C1") <> "" Then
Call MsgBox("You have not entered anything in D1." _
            & vbCrLf & "Please make an entry now, or delete the value in C1" _
            , vbExclamation, Application.Name)
Range("D1").Activate

End If
End If
Application.EnableEvents = True
End Sub

The second sub, you will need to change the value of myRange to the cell the user tabs to after leaving cell D1