Preventing a spreadsheet from closing

Hi Amanda,

Thank you for the forum question.


Yes everything is possible in Excel (almost).

Create a before close event in ThisWorkBook as below:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim LResult As Long

LResult = FileLen(ThisWorkbook.FullName)

MsgBox LResult


If LResult > 200000 Then
Cancel = True
End If

End Sub

You will not need the line MsgBox LResult, but this will tell you the size of the file in a message box. You will need to decide the max size. In my code above I have sat the max size to 200000 byte.

Good luck but let me know if you need more help.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector