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

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

The file is a true Template (*.xlt) and it works every time you open it
(including the cells that are in the proper time format).

The reason it is cleared is because the file is designed to interact with
the mainframe to gather more data than what I'm presenting. Anyway, once the
information gathering is complete the data is printed and that portion is
complete. There's no need to save. Having the option to clear the workbook
instead of closing the template and reopening does makes it easier for the
user to work on the next group of information. I hope that makes sense.

I do understand the purpose of a template where you can not change the
original design. What troubles me is how can I put instruction in the macro
to close the current file, not save, and then reopen the template file? The
button we have on the spreadsheet is called "Reset".

Hope that helps,

Thank you,

Tom

"Gord Dibben" wrote:

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