You are here:

Excel/Automatically open second WB

Advertisement


Question
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"
   Windows("Book1.xlsm").Activate
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()
   DDList
End Sub

Done.

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

put:

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.

Answer
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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jan Karel Pieterse

Expertise

Excel and Excel/VBA questions

Experience

Excel MVP

Organizations
Self employed Excel developer

Education/Credentials
Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

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

©2016 About.com. All rights reserved.