Why are you clearing after use?

If the workbook is created as a true Template(*.xlt) then you don't need to
clear and reset for re-use over and over.

Each time you open a workbook from the Template, that new workbook will be
clear and ready for entry of new data and to be "saved as" a new file name.

The original Template is never over-written so no need for clearing.


Gord Dibben MS Excel MVP

On Tue, 4 Nov 2008 07:35:00 -0800, TomP
wrote:

Using Office 2003 Excel

I have a spreadsheet saved in template format and has a button to clear the
workbook over and over again for reuse. Everything works fine except for the
time format. Column B (starting from A8, A9, etc... and below) uses a data
validation to select the times. The data validation box is set to "Allow
List", "Ignore Blank" is checked and "In-cell dropdown" is checked.

The problem is when I open the workbook and enable macros, the time format
works great. But when I clear the workbook, the time format for column B is
changed to a different format when using the drop down menu.

Below is the instruction I have and don't know how to keep the time format
consistent.

UnLock_Click
' Range("A8:AW38,E1:E5,H3,H1,A44:AW74").Select
Range("A8:AW38,E1,E2,E4,E5,H3,H1,A44:AW74").Select
Selection.ClearContents
ActiveSheet.Unprotect
Selection.Interior.ColorIndex = xlNone
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

I hope this makes sense. Thank you for your help!

Tom