VBA - mandatory cells in excel

1. You should place the code in standard form: Select it and click on the # icon

2. This may be what you are looking for…

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Dim msg As String
   Dim mandatoryCell As Range

   With Sheets("Sheet1")
      For Each mandatoryCell In Range("B7:B16", "B18:B20")
         If mandatoryCell.Value = "" Then
            msg = msg + mandatoryCell.Address & "  "
         End If
      Next
   End With

   If msg = "" Then Exit Sub
      ' otherwise
   msg = Replace(msg, "$", "")  ' remocve $ frpm addresses
   MsgBox "Please fill out these required fields on Sheet1:  " & vbCrLf & msg
   Cancel = True 'cancels the save event

End Sub

1. You should place the code in standard form: Select it and click on the # icon

2. This may be what you are looking for…

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Dim msg As String
   Dim mandatoryCell As Range

   With Sheets("Sheet1")
      For Each mandatoryCell In Range("B7:B11, B13:B16, B18:B20")
         If mandatoryCell.Value = "" Then
            msg = msg + mandatoryCell.Address & "  "
         End If
      Next
   End With

   If msg = "" Then Exit Sub
      ' otherwise
   msg = Replace(msg, "$", "")      ' remove $ symbols from addresses
   MsgBox "Please fill out these required fields on Sheet1:" & vbCrLf & msg
   Cancel = True     'cancels the save event

End Sub