To make multiple columns mandatory based on data in one of mandatory columns

[sarmadbari]

Hi, I am currently using the below code to make multiple columns mandatory in case of data being input in first column "A"
============================================================================================

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


Dim rngCell As Range
Dim lngLstRow As Long, lngTCols As Long
Dim lngRowCheck(1 To 4) As String


lngRowCheck(1) = "A"
lngRowCheck(2) = "B"
lngRowCheck(3) = "C"
lngRowCheck(4) = "E"


lngLstRow = ActiveSheet.UsedRange.Rows.Count


For i = 1 To UBound(lngRowCheck)
For Each rngCell In Range(lngRowCheck(i) & "2:" & lngRowCheck(i) & lngLstRow)
If rngCell.Value = 0 Then
MsgBox ("Please enter a value in cell " & rngCell.Address)
rngCell.Select
Exit Sub
End If
Next
Next i


End Sub

=========================================================================================
However, I am getting issue in case of having a table that it is taking the whole table as a range and keeps on giving pop up messages until all the columns in the table is not filled. I am new to VBA and any help in this regard will be highly appreciated. Please note that I may also add new columns to range or delete any from above code.

 

[mumps]

Are you saying that if the user enters a value in column A, you want to force data entry into columns B, C and E?

 

[sarmadbari]

Are you saying that if the user enters a value in column A, you want to force data entry into columns B, C and E?

yes, which is all ok as of now with this coding. The only issue that I am facing is that this code keeps on giving the message to enter value in a particular column until the whole table end (lets say, if I have 5 rows in a table starting from row 2 then the pop up message keeps on coming for column B until all 5 rows are not filled and so on. I wanted the pop up message to only appear if Column "C" have any value in a particular row so that user can be forced to fill other mandatory cells with in that row which in my case are columns "C,D,I,L,N,O,P, & T". Furthermore, I wanted this macro to work on a particular sheet not the whole workbook.

Any help in this regard would be highly appreciated. Many thanks.

 

[mumps]

Try:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.ScreenUpdating = False
    Dim rng As Range, colArr As Variant, i As Long
    colArr = Array("C", "D", "I", "L", "N", "O", "P", "T")
    For Each rng In Range("A2", Range("A" & Rows.Count).End(xlUp))
        If rng <> "" Then
            For i = LBound(colArr) To UBound(colArr)
                If Range(colArr(i) & rng.Row) = "" Then
                    MsgBox ("Please enter a value in cell " & Range(colArr(i) & rng.Row).Address(0, 0))
                    Cancel = True
                    Range(colArr(i) & rng.Row).Select
                    Exit Sub
                End If
            Next i
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub