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.

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.

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.

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.

Hi Chris,

You're absolutely correct. I missed the subtlety of the .Formula versus the
..Value. Implementing your suggestion enables the macro to work precisely as
I wanted.

You're correct in modifying the macro to update the date and service number
fields only if blank. That was code I was planning to add once I solved the
SendKeys problem.

Thanks for your input,
Arik


"Chris Marlow" wrote:

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.