Fields mandatory

Hello Community,

I am creating an excel sheet where if the user has filled a cell in column A, he has to fill input also on column B, C and D, otherwise, he shouldn't be able to save and close the file.

Does someone have an idea on how could be this enabled?

Thank you in advance,
Alina

Quote:

---

I am creating an excel sheet where if the user has filled a cell in column A, he has to fill input also on column B, C and D, otherwise, he shouldn't be able to save and close the file.

Does someone have an idea on how could be this enabled?

---

Yes

In the workbook code module. If you want to check all or most sheets, there's an easy way to do that also (edited a typo)

Code:

---

Option Explicit


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = NotOkay
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cancel = NotOkay
End Sub
   
   
Private Function NotOkay() As Boolean
    Dim rColA As Range, rCell As Range
   
    NotOkay = False
   
    With Worksheets("Sheet1")
        Set rColA = Nothing
        On Error GoTo 0
        Set rColA = Intersect(.UsedRange, .Columns(1))
        On Error GoTo 0
    End With
   
    If rColA Is Nothing Then Exit Function
       
    For Each rCell In rColA.Cells
        If IsEmpty(rCell) Then GoTo NextCell
       
        With rCell
            If IsEmpty(.Offset(0, 1)) Then
                Call MsgBox("Cell " & .Offset(0, 1).Address & " is empty", vbCritical + vbOKOnly, "Check Cells")
                NotOkay = True
                Exit Function
           
            ElseIf IsEmpty(.Offset(0, 2)) Then
                Call MsgBox("Cell " & .Offset(0, 2).Address & " is empty", vbCritical + vbOKOnly, "Check Cells")
                NotOkay = True
                Exit Function
           
            ElseIf IsEmpty(.Offset(0, 3)) Then
                Call MsgBox("Cell " & .Offset(0, 1).Address & " is empty", vbCritical + vbOKOnly, "Check Cells")
                NotOkay = True
                Exit Function
            End If
        End With
NextCell:
    Next
End Function

---

Thanks a lot Paul, it worked!
Cheers,
Alina

That macro required that the empty cells be 'fixed' on at a time

Another approach that I typically use is to do it all at once by shading the incorrect cells, making a Msgbox "There are 15 empty cells, shaded gray", and letting the user do all at once

Let me know; it's not much different from the original macro

Can you please provide me the code for that too?
Thank you in advance,
Alina

Maybe start with something like this

I added logic so that the Msgbox would not show on both the Close and the Save events, and added an option to save even with missing data (you can delete that if you really don't want it)

Code:

---

Option Explicit


Dim NotOkay As Boolean, bClosed As Boolean


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   
    If bClosed Then
        Cancel = False
   
    Else
        NotOkay = False
        CheckRequiredCells
        Cancel = NotOkay
    End If
End Sub






Private Sub Workbook_BeforeClose(Cancel As Boolean)
    NotOkay = False
    bClosed = False
    CheckRequiredCells
    Cancel = NotOkay
    If Not NotOkay Then bClosed = True
End Sub
   
   
Private Sub CheckRequiredCells()
    Dim rColA As Range, rCell As Range
    Dim n As Long, i As Long
   
    With Worksheets("Sheet1")
        Set rColA = Nothing
        On Error GoTo 0
        Set rColA = Intersect(.UsedRange, .Columns(1))
        On Error GoTo 0
    End With
   
    If rColA Is Nothing Then Exit Sub
       
    For Each rCell In rColA.Cells
        If Not IsEmpty(rCell) Then
            With rCell
                .Offset(0, 1).Resize(1, 3).Interior.ColorIndex = xlColorIndexNone
                For i = 1 To 3
                    If IsEmpty(.Offset(0, i)) Then
                        n = n + 1
                        .Offset(0, i).Interior.ColorIndex = 15
                    End If
                Next i
            End With
        End If
    Next


    If n > 0 Then
        If MsgBox("There are " & n & " cells (shaded Gray) that need filling" & vbCrLf & vbCrLf & _
            "Save anyway?", vbYesNo + vbQuestion, "Filling Cells") = vbNo Then
            NotOkay = True
        End If
    End If
End Sub

---

Thank you very much Paul!

So the difference here is that the second solution highlights all the cells that need to be filled and tells you how much of them there is, while the first one tells you the earliest cell that threw the error?

Or:
if you fill in something in column C, and columns D to J are obligatory:

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column & Target.Count = 31 Then Target.Offset(, 1).Resize(, 6).FormatConditions.Add(xlBlanksCondition).Interior.ColorIndex = 3
End Sub

---

Quote:

---

Originally Posted by FKemp

So the difference here is that the second solution highlights all the cells that need to be filled and tells you how much of them there is, while the first one tells you the earliest cell that threw the error?

---

Post #2 macro does them one at a time (since that's what the OP originally thought they wanted)

Post #6 was a suggestion based on the way I'd re-think the desired results, which is to high light all cells with missing data and say "Fix these"