How to keep spreadsheet open in the BeforeClose event

  • Thread starter Thread starter Wellie
  • Start date Start date
W

Wellie

In the WorkbookBeforeClose() sub, I perform "required"
fields data check. After performing the test and prompt
user with a message indicating which which fields are
missing data if there are data missing.

At present, as soon as I click the Ok button in the
message box. The spreadsheet is closed.

How can I keep the spreadsheet open after prompting the
above message s.t. I can move the cursor to the first
missing data field ?

Regards
 
Hi,

One way

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Range("a1").Value = "" Then
MsgBox "Data incomplete "
Cancel = True

End If


End Sub


Regards,
Cesar Zapata
 
Hi Wellie

For more cells you can use this
Change the range to yours

Dim myrange As Range
Set myrange = Worksheets("Sheet1").Range("A1:A6,C10,D12,G1:G3")
If Application.WorksheetFunction.CountA(myrange) < _
myrange.Cells.Count Then
MsgBox "fill in all cells"
Cancel = True
End If
 
If you want the sheet to remain open, use...

Cancel = True

....before prompting them with the missing data
 
Back
Top