• Technology
  • CFO.com | US

Spreadsheets: Open to Today’s Date

Cut and paste this simple macro into your workbook to make it open on the current date. Then add just a few more lines of code to customize the macro to your liking.

Reader David H. wins a copy of Office VBA Macros You Can Use Today for submitting this question: “I have a column which has each day of the month in it (e.g., 1, 2, 3, 4, etc.). This is what I’d like Excel to do: when I open the spreadsheet, I want to go to the cell that has today’s day of the month in it. For example, if today is October 1, 2011, I want Excel to go to the cell that has ’1′ in it for the 1st of the month. I think it’s pretty simple, but I am just not sure what the ‘trick’ is to make it do what I need.”

Ah, yes, this sounds simple — and it only takes two lines to solve it — but any time you need the cell pointer to move to a specific place automatically, you are going to need a macro. Don’t worry, though: this macro is a short one and this article will walk you through adding it to your workbook.

Use a File Extension That Allows Macros

Back in the good old days, all Excel files were stored as .XLS files and they all allowed macros. Starting in Excel 2007, you had a choice of .XLSB, .XLSX, .XLSM, or .XLS. Out of those four file types, macros work fine in 75% of them. Unfortunately, the one file type that disallows macros was the default file type in Excel 2007/2010. If your file is currently stored as an .XLSX file, you need to use Save As and save the file as one of the other file types. After the Save As, close the file and reopen to enable the macro language.

Allowing Macros in Excel

By default, macros are turned off in Excel. Follow these steps one time to allow macros:
• In Excel 2003 or earlier, choose Tools, Macro, Security. Change the setting to Medium.
• In Excel 2007 or later, type Alt T followed by M then S. Choose “Disable All Macros With Notification.”

With both of these settings, you will receive a notification when you open a workbook with macros. If you are the author of the macros and know what they do, you can choose to enable them.

Find the Workbook Code Pane

Even if you’ve done macros before, this special one is an event macro that runs automatically when the workbook opens. Follow these steps to get to the right place for your macro:
1. Open your workbook.
2. Press Alt F11 to open the VBA Editor.
3. Press Ctrl R to display the Project Explorer. You will see a bold VBAProject entry for every open workbook or open Add-In (see Figure 1).

Fig. 1

MrExcel 3-16-11 Fig01

4. Click the Plus sign to the left of your workbook. You will see a folder called Microsoft Excel Objects.
5. Click the Plus sign to the left of Microsoft Excel Objects. You will see all of the worksheets in your workbook. At the bottom of the list is an entry called ThisWorkbook (Figure 2, below).
6. Double click on ThisWorkbook. A blank code module will appear on the right.
7. At the top of the code pane, there are two dropdowns. Open the left dropdown and choose Workbook. The right dropdown will automatically change to Open and the start of a Workbook_Open macro will appear in the code pane.

Discuss

Your email address will not be published. Required fields are marked *