Mark has attended:
Excel VBA Intro Intermediate course
Excel Advanced - For Power Users course
Excel PowerPivot course
Excel Dashboards for Business Intelligence course
Workbook Open/Close Related Code
I have a spreadsheet where I have the code which hides the formula bar, the Excel ribbon and turns the calculations to manual when the file is opened.
Is there any equivalent code which will turn these back 'on' when the file is closed?
RE: Workbook Open/Close Related Code
Hi Mark
Thanks for getting in touch. If you have the code for turning them on, turning these events off again are often the reverse property. e.g.
Show the formula bar:
Application.DisplayFormulaBar = True
Turn on automatic calculation:
Application.Calculation = xlAutomatic
Turn on the ribbon:
Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"",True)"
I hope this helps. Let us know if you have any problems.
Kind regards
Gary Fenn
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
RE: Workbook Open/Close Related Code
Hi Gary,
I've got the code to turn these all back on, it's the relating it to the workbook closing is where I'm coming unstuck. The code for the private_sub to turn these all on is below;
Private Sub Workbook_Open()
Application.Calculation = xlManual
Application.DisplayFormulaBar = False
Application.DisplayFullScreen = True
End Sub
I was hoping that there would be a Workbook_Close() entry?
RE: Workbook Open/Close Related Code
Hi Mark
Thanks for your reply. The event is slightly oddly named:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
You can find this by going to the ThisWorkbook object, choose the drop-down for Workbook and on the right browse the events.
Kind regards
Gary Fenn
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
