Save As .xlsm Only

With the advent of Excel 2007 Microsoft introduced new file extensions for Excel.  Instead of having only .xls for all workbooks we now have .xlsb for Excel Binary Workbooks, .xlsm for Excel Macro-Enabled Workbooks, .xlsx for Excel Workbooks and a multitude of other formats from Templates to Text.  One of the potential issues of these different formats when using Macros is the loss of the Macros when the workbook is not saved as a Macro-Enabled Workbook.  Even though the User receives a message that the Macros will be removed the message is often ignored resulting in a copy of the workbook minus the original Macros.

One solution to this issue is to prevent users from saving your workbook with any file extension other than .xlsm.  The code below will prevent this.

 
This code has to be in the Workbook module rather than a regular module.  To access the Workbook module click the Developer tab and then click the Visual Basic icon to display display  the Visual Basic editor.

Right click on ThisWorkbook and select View Code

Enter the code in the the Worksheet Module, close the editor and save the workbook.

If you have questions regarding this let me know and I'd be happy to help if I can.

Date Time Stamp

Received a request from my son last week for a way to enter a date/time stamp in a cell when any value is entered in another cell.  Also, if the entered value is removed, remove the time stamp.  He's using Excel to track specific information regarding projects assigned to him.  When he enters a Project name in Column A he wants the date/time stamp in Column C of the same row.  When the Resolution of the Project is entered in Column E a date/time stamp is placed in Column F of the same row.

This was accomplished by using the Worksheet_Change event.  You can do a Google search on "Worksheet_Change" more detailed information about this event but suffice to say that it allows you to do something when a cell is changed on a worksheet.  The only way I've previously used this event was to run a macro but in this case we're simply wanting to insert a value into a specific cell.  

Here's the basic layout of the worksheet that needs the date/time stamp in columns C and F.

The following code is written to the Private Module of the Worksheet where it will be used rather than a general Module in the workbook.  The easiest way to do this is to right click on the worksheet tab and select View Code.  Paste this code into the Private Module.

There may be (and probably is) a better way to accomplish this but this how I did it.  If you know of a better or more efficient way to accomplish this I would appreciate hearing from you.