Mandatory Cell based on Criteria

[steve.plewa]

All. I'm an intermeadiate excel user that needs your help. I would like to create a condition to have a certain cell become required if another cells has information in it. I found the following macro that works perfectly with one acception. This is a log sheet that I need this to work in so I need it to incrementally go from one line to the next one information is entered in to my required cell (which will incrementally move down to the next row when data is added). I hope I've explained this so everyone understands what I'm asking.

Example: When information (a date) is entered in to Column A I need Column J of that exact same row to become a required cell to be complete.


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set CloseRng = ActiveSheet.Range("j5")
For Each c In CloseRng
If IsEmpty(c) Then
MsgBox "A required Cell is blank"
Cancel = True
Exit Sub
End If
Next c
End Sub

 

[JaneG]

Hello Mr Excel, I'm new to the forum and it's a VERY long time since I've used VBA. I wondered if you could help me with a problem. I found this thread and it has been very useful, but I'm still doing something slightly wrong.

I basically want to make Cells I23:I27 & J23:J27 mandatory, but only if there is a value in the corresponding cell in column G. If there is nothing in the cell in column G, then the warning should not occur. I used your code below, but I'm getting the warning even if there is no value in the cell in column G. Please can you help?

Dim CloseRng As Range
Dim c As Range
With ActiveSheet
Set CloseRng = .Range("F23:F" & .Range("I" & .Rows.Count).End(xlUp).Row)
End With
For Each c In CloseRng
If IsEmpty(c) Then
MsgBox "Either the Panel Count or Gross Value is missing - please complete before printing"
Cancel = True
Exit Sub
End If
Next c
End Sub

 

[JaneG]

Thanks for the response but I'm not sure I know what you mean. Perhaps I'm not explaining myself properly. Would it help if I sent you the workbook?

Cheers

Jane