Re: QDE (Quick Date Entry)

From: hgrove (hgrove.1bz0h2_at_excelforum-nospam.com)
Date: 09/02/04 

Date: Thu, 2 Sep 2004 14:28:16 -0500

Frank Kabel wrote...
>>Have you considered formatting the QDE date entry range as
>>Text when the work*** is activated? Then leading zeros
>>would be retained after entry.
>
>This was one alternative but we decided against it. This would
>prevent formulas entered in the range and also could/would
>cause problems with the processed date values (alignment,
>etc.). So just a design decision with benefits and drawbacks

Several problems with your rationale.

1. Formulas. You're already processing entered cells. It's simple to
check if the first character in the cell is =, and if so reformat the
cell as General (or clear the cell's format to return it to the
work*** default) then set the cell's .Formula property equal to it's
Value property. If the user wants to enter a text constant beginning
with =, oblige them to enter an initial apostrophe (as they'd need to
without QDE), and process cells with initial apostrophes the same ways
as those beginning with =. Heck, you could use the same code for both
situations.

If c.Formula Like "['=]*" Then
c.ClearFormats
c.Formula = c.Value
End If

2. Alignment. Cells formatted as Text would be left-aligned by default.
When the user makes an entry, if the entry could be converted into a
date, what prevents you from changing the cell's format as well as its
contents? If you change the cell's format to anything numeric then
change its contents to something Excel will interpret as a date, the
resulting date should be aligned the same way any other numeric entry
would be aligned under the workbook's Normal style, no?

I can't believe you guys really thought this through.

>> . . . But what about 5-digit numbers? Would 21877 be
>>treated as a short date to be converted to 18-Feb-1977 or as
>>the date serial for 23-Nov-1959? Or 40109 as 1-Apr-2009 or
>>23-Oct-2009? How could QDE cope with dates actually entered
>>as standard Excel dates that just happened to correspond to
>>date serial numbers that look like possible 5-digit short dates?
>
>QDE would interprete this entry as a short date (so depending
>on your settings: 18-Feb-1977 and 1-Apr-2009 respectively / for
>me: 21-Aug-1977 and 4-Jan-2009). All numbers entered are
>treated as 'short date entries' and not as serial date values.
>This is another reason for using the Selection_Change event -
>or to be more precise to change the format to 'Text' before
>entering a value. This way Excel does not convert the 5 digit
>entries to a date value (using the entry as serial number).
..

In other words, QDE would mung up (jargon for fubar) standard Excel
date entries. This would be another advantage of having QDE process
only cells initially formatted as Text and not use SelectionChange
event handlers to change formats. When a cell has been processed, it's
going to be in a date format presumably, so subsequent entries when the
format hasn't changed could be standard Excel date entries, and wouldn't
be reprocessed by QDE. To allow users to change entries, you could add
another hotkey (E) that would store the cell's .Text property, reformat
it as Text, then enter the stored .Text property as its .Value
property.

>And a stated above: You could achieve something similar with
>formating the entire range as 'Text' but then you have other
>drawbacks

I believe you've overestimated the drawbacks. 

---
Message posted from http://www.ExcelForum.com/