Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

preventing spreadsheet closing

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Preventing a spreadsheet from closing

Preventing a spreadsheet from closing

ResolvedVersion 2013

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

 

Training courses

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Autofit column width – Excel (all versions)

a. Highlight the column or columns you wish to alter the width of. You do this by clicking on the grey button at the top of the column showing the column letter. Click and drag on these letters to select more than one column.
b. Double click the dividing line between the columns. This dividing line is the break between the columns on the column headers (grey buttons showing the column letter at the top of each column). When you hover your mouse over one of these dividing lines the point will change and show an arrow pulling a line in two directions. When you have this mouse pointer you should double click to get Excel to automatically set the column width to fit the contents of the column (autofit)

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.