98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Workbook Open/Close Related Code
Workbook Open/Close Related Code
Resolved · Medium Priority · Version 2007
Tue 19 Feb 2013, 10:39Delegate Mark said...
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?
Tue 19 Feb 2013, 12:05Trainer Gary said...
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
Tue 19 Feb 2013, 12:09Delegate Mark said...
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?
Tue 19 Feb 2013, 12:17Trainer Gary said...
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
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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Keyboard Shortcuts to Add Rows or ColumnsCouple of other keyboard shortcuts. Shift+spacebar selects a row, Ctrl+spacebar selects a column. Select either row or column (or several) and use Ctrl and + to insert or Ctrl and - to delete rows or columns. |
