To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips Page.
Reader David H. wins an autographed copy of VBA & Macros for Microsoft Excel from CFO and MrExcel.com for his question: “I think it’s pretty simple, but I am just not sure what the ‘trick’ is to make my spreadsheet work in the following way: I have a column which has each day of the month in it (e.g., 1, 2, 3, 4, etc.). When I open Excel, I’d like the spreadsheet to go to the cell that has today’s day of the month. For example, if today is Oct. 1, 2010, I’d like Excel to go to the cell that has ’1′ in it, which in this case represents the first of the month. I use the spreadsheet to list projects and other tasks that are due on each day of the month, categorized by day (e.g., 1st day).”
This trick uses a very small macro known as the Workbook_Open macro. This tiny macro runs every time the workbook opens, and makes use of a system variable called Date. Note that Date will return a value such as 4/20/2011. To find the date of the month, you would combine the Excel DAY function with the Date variable to return a number such as 20: Day(Date).
Use a File Type That Allows Macros
Macros will work with files saved as .XLS, .XLSB, or .XLSM. They will not work with files saved as .XLSX. Unfortunately, the default file type in Excel 2007 and Excel 2010 is often .XLSX. Use the Save As command to save your file as a different file type if it is currently stored as .XLSX.
Allow Macros on Your Computer
In Excel, press Alt+T followed by the letters M and S. You will get to the macro security settings. In Excel 2003, avoid “Very High” and “High.” In Excel 2007 and newer, avoid “Disable All Macros Without Notification.”
The Workbook_Open Macro Is Entered in a Special Code Window
Unlike general macros that you record, the Workbook_Open macro needs to be typed or pasted into the code pane that is attached to ThisWorkbook. Here is how to arrive at that code pane:
1. From Excel, press Alt+F11 to open the VBA Editor. (Some newer keyboards require you to press the F-Lock key before using the function keys.)
2. In the VBA Editor, press Ctrl+R to display the Project – VBA Project pane. You will see a list of all open workbooks.
3. Find your workbook in the list. Click the + sign to the left of your workbook to expand the list of workbook objects.
4. If necessary, click the + sign to the left of the Microsoft Excel Objects folder. You should now see a list of worksheets in the workbook. At the bottom of the list is an entry called ThisWorkbook.
5. Double-click the ThisWorkbook entry. You will now have an empty code pane on the right with two dropdowns at the top.
6. From the left dropdown above the code pane, choose Workbook (it is the only item in the dropdown).