I am attempting to block all manual entries frm being placed in a range of
cells used for time keeping. I want to force all users to use the
"ctrl,shift,;" command only to ahave a time placed in the cell, but not allow
them to manually type in the times.

Any Assistance will be greatly appreciated!

I don't think you can do this.

Maybe you could lock the cell, protect the sheet and then give the users a macro
to fill the activecell with the current time (and date??).

Your code may have to unprotect the worksheet, do the work and then protect the
worksheet.

But be aware that worksheet protection is easily broken.

Jim wrote:

I am attempting to block all manual entries frm being placed in a range of
cells used for time keeping. I want to force all users to use the
"ctrl,shift,;" command only to ahave a time placed in the cell, but not allow
them to manually type in the times.

Any Assistance will be greatly appreciated!

--

Dave Peterson

Jim

Maybe event code which places the time a cell is altered then locks the time
cell from futher change?

First select column A and FormatCellsProtection. Disable "locked"

Add this code to the sheet module........right-click on sheet tab and "View
Code". Copy/paste into that module

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
'column = 1 is "A" column
ActiveSheet.Unprotect Password:="justme"
n = Target.Row
If Target.Value < "" _
And Target.Offset(0, 1).Value = "" Then
With Target.Offset(0, 1)
.Value = Now
.Locked = True
End With
End If
End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme"
End Sub

The time will be stamped into column B when column A cell is altered.


Gord Dibben MS Excel MVP

On Tue, 15 May 2007 04:17:00 -0700, Jim wrote:

I am attempting to block all manual entries frm being placed in a range of
cells used for time keeping. I want to force all users to use the
"ctrl,shift,;" command only to ahave a time placed in the cell, but not allow
them to manually type in the times.

Any Assistance will be greatly appreciated!

In addition................

Note: you will want to lock the project also to prevent users from looking at
the code to get the password.

With your workbook open.

Alt + F11, CTRL + r

Right-click on your workbook/project and "Properties"

Select "Protection" tab and "lock project for viewing".

Enter a password to view.

Note: workbook must be saved/closed and re-opened before the locking takes
effect.


Gord

On Tue, 15 May 2007 09:23:04 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Jim

Maybe event code which places the time a cell is altered then locks the time
cell from futher change?

First select column A and FormatCellsProtection. Disable "locked"

Add this code to the sheet module........right-click on sheet tab and "View
Code". Copy/paste into that module

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
'column = 1 is "A" column
ActiveSheet.Unprotect Password:="justme"
n = Target.Row
If Target.Value < "" _
And Target.Offset(0, 1).Value = "" Then
With Target.Offset(0, 1)
.Value = Now
.Locked = True
End With
End If
End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme"
End Sub

The time will be stamped into column B when column A cell is altered.


Gord Dibben MS Excel MVP

On Tue, 15 May 2007 04:17:00 -0700, Jim wrote:

I am attempting to block all manual entries frm being placed in a range of
cells used for time keeping. I want to force all users to use the
"ctrl,shift,;" command only to ahave a time placed in the cell, but not allow
them to manually type in the times.

Any Assistance will be greatly appreciated!