Mandatory cells

**john.mk** · 02-21-2010, 03:31 PM

Hi, am very new to excel and need to make cells C2, D3, E3, D4,E4, D5, E5,D6, E6, D7, E7, D8, E8, D9, E9, D10, E10, D11, E11, D12 & E12 mandatory in an open sheet.
please help to stop closing the sheet before all these cells are filled. you may reach me at "[email protected]"

**jj72uk** · 02-21-2010, 03:41 PM

So just to confirm.

You want the above listed cells filled before the excel sheet is allowed to close?

**Palmetto** · 02-25-2010, 11:03 PM

Try this code in the workbook_beforeclose module.

Keep in mind that users have the option to NOT enable macros when prompted and can thus defeat any VBA code.

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim msg As String
    
    msg = "Warning: cells C2 and D3:E12 are missing data"
    msg = msg & vbLf & vbLf & "Please complete the missing entries"
    
    With Sheet1
        If .Range("C2") = vbNullString Or WorksheetFunction.CountBlank(.Range("D3:E12")) > 0 Then
            MsgBox (msg), vbCritical
            Cancel = True
        End If
        Application.Goto reference:=Sheet1.Range("C2"), scroll:=True
    End With

End Sub