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