Mandatory Field Excel (via VBA) RRS feed

  • Question

  • I would like to launch a message box / notification whenever a cell is empty (if another cell in the same row is not empty).

    In other words: when a cell in column A contains input then the cell in same row cannot be empty (must contain input).

    I already installed via data validation a drop down list in order to have the correct input but this does not prevent the cell from being empty (drop down is needed because the spelling is important as cell is linked via vlookup).

    Can someone help by developping a VBA code based on this problem?

    Possible scenarios:

    • If A1 = empty: no problem
    • If A1 <> empty and G1 <> empty: no problem
    • If A1 <> empty and G1 = empty: a message box with instruction to fill in G1 must appear (choice from drop down).

    This for a specific range (number of rows).

    Thanks in advance!

    • Edited by DieterDJ1 Monday, August 13, 2018 3:17 PM
    Monday, August 13, 2018 3:16 PM

All replies

  • Hi,

    You can use the Worksheet_Change event to track the changes in your sheet this way:

    Private Sub Worksheet_Change(ByVal Target As Range)
       MsgBox Target.Cells.Row
    End Sub

    In the above code, you will catch the row number and from there you can start validating all the cells in that row.

    In order to use MsgBox to tell you what you are missing, you could end with infinite message boxes if the validation is not handled well. I would think of a simple solution is by changing the background color of the mandatory cells to a red if they are still empty like the following code:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim row As Integer 'The number of the row which has been changed/altered.
        Const firstCell As Integer = 1 'The index of the first cell in the row which should include data.
        Const lastCell As Integer = 5 'The index of the last cell in the row which should include data.
        Dim i As Integer 'To handle the for loop counter.
        row = Target.Cells.row
        For i = firstCell To lastCell
            If Cells(row, i) = "" Then
                Cells(row, i).Interior.Color = 255
                Cells(row, i).Interior.Pattern = xlNone
            End If
        Next i
    End Sub

    Monday, August 13, 2018 4:13 PM
  • you could also use conditional formatting.

    in this case you don't need VBA

    Tuesday, August 14, 2018 9:11 AM