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?

 

[AnotherHiggins]

Please repost this VBA-specific question in forum707.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

He did post this in Forum707 thread707-1481177.

But I did point out that Data > Validation might be a better way to go.

Skip,

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

 

[AnotherHiggins]

That makes sense, Skip. I didn't really look at what was being requested, just that VBA code was being sought.

Well, slickp007, did you try your hand at Data > Validation? Are you having problems with it?

When it comes time to validate the second cell dependent on the first, have a look here:

http://www.contextures.com/xlDataVal02.html

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[slickp007]

I'm having real problems with data validation for this. How can i force a cell not to be null using data validation?

 

[SkipVought]

I believe that I was the one who directed your toward Data > Validation.

It seems that ONLY when the cell is in the EDIT mode, will this kind of validation work to prevent no value from being entered. Data > Validation cannot require that a value be entered in a cell.

Another option:

UNLOCK all data entry cells.

Protect the sheet. This will enable tabbing from one data entry cell to the next.

This is the code that you will need.
[tt]
Insert a MODULE and declare a Public Variable

Public sPrev as string

Activate the ThisWorkbook Object Code Window and paste this code

Private Sub Workbook_Open()
    Sheets("[b]YourSheetName[/b]").Activate
    sPrev = ActiveCell.Address
End Sub

Activate the Sheet Object Code WIndow for the sheet with the data and paste this code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range(sPrev).Value = "" Then
        Application.EnableEvents = False
        Range(sPrev).Select
        Application.EnableEvents = True
    Else
        sPrev = Target.Address
    End If
End Sub

[/tt]
Add whatever message you wish.

Sorry for the miss-q.

Skip,

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