Trapping Cell Input BEFORE Cell Updates

**PosseJohn** · 10-31-2011, 03:03 AM

Greetings,

I have a Worksheet that provides a cell for the user to enter a Date/Time.

The cell is formated as Date/Time.

I've had some feedback from users that it would be nice just to enter the time without a semi-colon to obtain the current date + the time they entered.

For instance, if they enter 2115, the cell would show the current date with time of 21:15,
i.e. 10/31/2011 21:15.

My problem is that if they enter 2115, the cell updates and formats the contents to 10/15/1905 00:00, THEN calls the WORKSHEET CHANGE event.

Is there a BEFORE CHANGE event that isn't documented?

Any other solutions?

**foxguy** · 10-31-2011, 03:12 AM

There is no BeforeChange event.
If I was doing this, I would format the cell as "Text" and have Worksheet_Change() put the value back as text that looks like a date.

Please Login or Register  to view this content.

**PosseJohn** · 10-31-2011, 04:21 AM

Your response allowed me to further my code. Here's what I ended up with...

**PosseJohn** · 10-31-2011, 04:23 AM

Sorry

Here's what I ended up with...

Please Login or Register  to view this content.

Thank again for the response.

**foxguy** · 10-31-2011, 07:01 AM

I strongly recommend that you Name $F$3 something. At a later date you may wish to redesign the sheet and move $F$3 to some other place. If the macro is looking for $F$3 then it will crash when the cell is moved. But if the cell is Named "Date", or "RptDate" or anything and the macro is looking for Range("Date") instead of Range("F3") then it will still find it when the cell is moved.