ccarmock

Depending upon how sophisticated your users are and whether or not they enable
macros upon opening the workbook you can achieve most of what you want.

First, unlock columns A and B then protect the sheet with a password("justme")
is example only.

The Sub following will enter a date in column A and lock that cell in Column A
but leaving rest of column A unlocked.

B must remain unprotected in order for users to enter data.

Add the code below to the sheet module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col B
'if a date is in A, it won't change when B is updated
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 2 Then
SHEETUNPROTECT
N = Target.Row
If Excel.Range("B" & N).Value < "" _
And Excel.Range("A" & N).Value = "" Then
Excel.Range("A" & N).Value = Now
Excel.Range("A" & N).Locked = True
End If
End If
enditall:
Application.EnableEvents = True
SHEETPROTECT
End Sub

Now copy the following macros to a new general module in the workbook.

Sub SHEETPROTECT()
ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub

Sub SHEETUNPROTECT()
ActiveSheet.Unprotect Password:="justme"
End Sub

Right-click on the workbook/project and select VBAProject Properties then
"Lock project for viewing"

You can password protect this module so prying eyes can't see it and obtain
the password("justme").

You now save the workbook and close Excel....the VBAProject locking won't come
into effect until Excel is closed out fully.

If the users enable macros and if no one of them knows how to crack a
VBAProject password, you should be good to go.


Gord


On Fri, 30 Sep 2005 12:30:06 -0500, ccarmock
wrote:


One further question on this - when I lock the spreadsheet (as I dont
want the date to be modified by the user, the system can no longer
update column A with the date of the change.

To achieve this I have column B set to allow Everyone to make changes
while the rest of the sheet requires a password to edit ranges.

I guess I can get the Macro to turn off protection and turn it on
again? However would this mean storing the password in the Macro?
Again not an issue, but I would then need to be able to prevent the
user from looking at the Macro code to discover the password - is that
possible?

Or alternatively is there the concept of allowing the 'SYSTEM'
permission to update a range of cells, this might be a cleaner
approach.