Thursday, 30 October 2014

Workbook Event BeforeClose to Prevent the Closing of a Workbook when Specific Cell Value is Blank

How to Prevent the Closing of a Workbook with WorkBook Event when Specific Cell Value is Blank 

We can do this by using the WorkBook Event "BeforeClose". Just use this Code in this Event Module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Check to see if Cell C5 is blank
If Sheets("Sheet1").Range("B5").Value = "" Then
    
'If Blank: Cancel the Closing of WorkBook and Tell the User
    Cancel = True
    
    MsgBox "Pleas Fill Cell B5 with Value then Try to Close the Workbook", vbOKOnly, "Hi ! The Cell B5 Can Not be Blank"

'If Not Blank; Save and Close the Workbook
Else
    ActiveWorkbook.Close SaveChanges:=True
End If

End Sub

WorkBook Event Image View :



Explanation :
Suppose a Worksheet is there where the Range("B5").Value is Mandatory.
As B5 is Mandatory Cell which should not be Blank. Though if you try to Close the Workbook without filling that B5 Cell , then you get the following Message alert as per about Event.



Thanks,
TAMATAM

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts