Preventing a spreadsheet from closing

Resolved · Medium Priority · Version 2013

Fri 15 Apr 2016, 16:39 Delegate Amanda said...

Amanda has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel VBA Advanced course
Excel Dashboards for Business Intelligence course
Managing Upwards course

Preventing a spreadsheet from closing

Hi Jens (or other trainer);

Would it be possible to have code in VBA that prevents the closing of a workbook if the file-size is over a certain amount or if there are more than xxxxx columns or rows?

Many thanks,
Amanda

Fri 15 Apr 2016, 21:39 Trainer Jens said...

RE: 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

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Navigate with keyboard without losing your active cell

If you like using your keyboard to scroll through your excel document, but want to keep your active cell the same, use the scroll lock, and then use your arrow keys to navigate around the document.

View all Excel hints and tips