Make cells (in a range) mandatory if another cell(another column range) in row is filled

[hussainkottakkal]

Hi there,

Your support is required.

I need an error msg to prompt out and prevent user from saving the file if the particular cell is not fill in.

Meaning if cell in the range C:C is filled up, Range H:H will have to be filled up before the user can save the file.


Thanks!

 

[Akuini]

@hussainkottakkal, welcome to the Forum.
Let's say the range are "C1:C5" & "H1:H5" in "Sheet1".
Try this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

With Sheets("Sheet1")
If WorksheetFunction.CountBlank(.Range("C1:C5")) = 0 And WorksheetFunction.CountBlank(.Range("H1:H5")) > 0 Then
    MsgBox "You must ..."
    Cancel = True
End If
End With

End Sub

 

[hussainkottakkal]

@hussainkottakkal, welcome to the Forum.
Let's say the range are "C1:C5" & "H1:H5" in "Sheet1".
Try this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

With Sheets("Sheet1")
If WorksheetFunction.CountBlank(.Range("C1:C5")) = 0 And WorksheetFunction.CountBlank(.Range("H1:H5")) > 0 Then
    MsgBox "You must ..."
    Cancel = True
End If
End With

End Sub

Thanks Akuini, I tried with the script that you provided. But, still this is not giving me the result. Even after entering the given vb i am able to save file without filling column H. Kindly see the attached snap of what I want. Thanks

​

 

[Akuini]

My code assumed that "C1:C5" is filled up whille your example shows only "C1:C3" is filled up.
Try this version instead:
Note: you must put the code in ThisWorkbook module.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim n As Long
With Sheets("Sheet1")
n = .Range("C" & .Rows.Count).End(xlUp).Row
If WorksheetFunction.CountBlank(.Range("C2:C" & n)) = 0 And WorksheetFunction.CountBlank(.Range("H2:H" & n)) > 0 Then
    MsgBox "You must ..."
    Cancel = True
End If
End With

End Sub

 

[hussainkottakkal]

My code assumed that "C1:C5" is filled up whille your example shows only "C1:C3" is filled up.
Try this version instead:
Note: you must put the code in ThisWorkbook module.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim n As Long
With Sheets("Sheet1")
n = .Range("C" & .Rows.Count).End(xlUp).Row
If WorksheetFunction.CountBlank(.Range("C2:C" & n)) = 0 And WorksheetFunction.CountBlank(.Range("H2:H" & n)) > 0 Then
    MsgBox "You must ..."
    Cancel = True
End If
End With

End Sub

Thank you very much... it worked..!!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.