You are here:

Excel/Automatically open second WB


QUESTION: I have drop down lists (using Data Validation) in a WB called "Book1".

I want "Book2" to open automatically whenever "Book1" is opened so that my drop down lists will work (drop downs are in Book2).

I want "Book2" to close automatically whenever "Book1" is closed.

I recorded a Macro which does open it, however it doesn't do so automatically. There will be other users now and I would rather not have then have to run a macro if it can happen automatically.

Here is what I have been using:

Sub DDLists()
' DDLists Macro
' Opens Book2 (drop down lists), so that they can be used in this WB.
' Keyboard Shortcut: Option+Cmd+Shift+A
   Workbooks.Open Filename:="Macintosh HD:Users:Josh:Documents:Book2.xlsx"
End Sub

ANSWER: Hi Jason,

You can easily achieve that by using the Workbook_Open event.

Open the VBA editor and double-click on the ThisWorkbook entry in the VBA project of the workbook.
At the top of the codepane, you will find two dropdowns. Click the left-hand dropdown and select "Workbook".

This piece of code will be entered in your window:

Private Sub Workbook_Open()
End Sub

Modify that so it looks like:

Private Sub Workbook_Open()
End Sub


---------- FOLLOW-UP ----------

QUESTION: Thank you very much. It opened just as you said. Is there a way to have it automatically close as well? Lastly, is there a way to keep the second WB hidden to the user?

ANSWER: Hiding:

Immediately after this line:

Workbooks.Open Filename:="Macintosh HD:Users:Josh:Documents:Book2.xlsx"


ActiveWorkbook.Windows(1).Visible = False

And to close the file, add this to the thisworkbook module of book1:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Workbooks("Book2.xlsx").Close False
End Sub

(change False to true if changes need to be saved)

---------- FOLLOW-UP ----------

QUESTION: I really appreciate your help on this. You have ignited a desire for me to learn VBA. Do you have any suggestions for books?

My experience level with Excel is having taken levels 1-3 and an "Advanced Topics" class at a community college (Excel 2010). We talked briefing about Macros and VBA and did very little practice with it. We spent the majority of time on doing things with the ribbon and common functions/formulas.

Hi Jason,

A good start is the Excel XXX VBA programming for Dummies book by John Walkenbach (version does not matter very much, but best to take at least the 2007 one).

Also good are the step-by-step books by Microsoft Press.

Always start by trying to solve your problem using built-in functionality though, that is usually the most efficient way.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Jan Karel Pieterse


Excel and Excel/VBA questions


Excel MVP

Self employed Excel developer

Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...

©2016 All rights reserved.