A
Arik Otis
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("C33").Select 'Data entry field is two merged cells
Application.SendKeys "^; ^+;", True 'Automatically populate with static
Range("C44").Select ' current date and time
Application.SendKeys "^; ^+;", True 'Send Cntl+; (space) Cntl+Shift+;
Range("C34").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("C55").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 C55, 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.
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("C33").Select 'Data entry field is two merged cells
Application.SendKeys "^; ^+;", True 'Automatically populate with static
Range("C44").Select ' current date and time
Application.SendKeys "^; ^+;", True 'Send Cntl+; (space) Cntl+Shift+;
Range("C34").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("C55").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 C55, 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.