Fields mandatory

[alinac]

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

[Paul_Hossler]

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)



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

[alinac]

Thanks a lot Paul, it worked!
Cheers,
Alina

[Paul_Hossler]

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

[alinac]

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

[Paul_Hossler]

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)



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

[alinac]

Thank you very much Paul!

[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?

[snb]

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


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

[Paul_Hossler]

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"