Arik,

By setting in code;

Range("C3").Value=CLng(Now()) 'sorry lng not int - you get an overflow with
int

The cell C3 on the active sheet will contain a value ... not a formula. It
will not update unless the code is re-run - which is the same effect as your
code (I think).

To get the behavior I think you are describing I would need the cell to have
a formula that returned the date. I would need to use ...

Range("C3").Formula="=TODAY()"

.... but that is not what you want.

You may need to change your Workbook_Open to only timestamp the cells if
they are empty - to stop the code updating the cells on opening the file. But
that is opening/closing the file, not using the form (unless that involves
opening/closing the file).

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Arik Otis" wrote:

Hi Chris,

Thanks for your response. The reason I didn't use Now(), Today() or
CInt(Now()) is that the result is not a static date and/or time. In other
words, anytime there's an update or recalculation on the form, the the date
and time are updated accordingly.

For this form, I can't have a date and request number that changes everytime
the form is updated. The integrity of the database would be lost. The only
way that I know of to get a static date is control key + semicolon (cntrl+;)
. Only the only way I know of to get a static time is control key + shift +
semicolon (cntrl+shift+;).

Regards,
Arik

"Chris Marlow" wrote:

Arik,

I'd try to avoid SendKeys is at all possible, within the confines of Excel
there is always a better way.

Try using code like;

Range("C3").Value=Now() 'Date with time
Range("C3").Value=CInt(Now()) 'Date without time

To insert the time.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Arik Otis" wrote:

I created an Excel 2003 form for users to create a simple database of work
orders. I wanted the form to automatically date and timestamp the submission
of the work order as well as assign a unique request number (I just used the
serial version of the date for that) to the form. So I automatically
populated these fields when the form was opened and then locked the cells so
the user couldn't change the information. The code is as follows:

Private Sub Workbook_Open()
Range("C3:D3").Select 'Data entry field is two merged cells
Application.SendKeys "^; ^+;", True 'Automatically populate with static
Range("C4:D4").Select ' current date and time
Application.SendKeys "^; ^+;", True 'Send Cntl+; (space) Cntl+Shift+;
Range("C3:D4").Select ' to enter static date and time
Selection.Locked = True 'Lock fields so automatically
populated
Selection.FormulaHidden = False ' data can't be changed by user
ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True 'Protect the worksheet so user can
only
Range("C5:D5").Select ' enter data in unlocked cells
End Sub

What happens when this is executed is inconsistent. Sometimes I get no data
put in the cells and other times the data for both calls to SendKeys ends up
crammed in cell C5:D5, the unlocked, unprotected cell!!!

Any advice on what I'm doing wrong or another way to do what I've described
(in Excel) would be greatly appreciated.