Hi Everybody,

Is there a way to dynamcally to protect a cell after the user's input upon
saving the file? What I am looking for is to prevent users from making
changes once the file is saved after their input. I am using this to track
vacation, personal day off & sick day dates and hours taken. The users are
given a date col & hrs col to input the date, I would like the input be
protected once the file is saved but the rest of the
empty cells in the col are still unprotected for future input.

Vac Date Vac Hrs.
1/23/2005 8 (Theses entries are protected after saving the file.)
2/23/2005 4
(Blank cells in the range are left
unprotected.)

Thanks for your time and help!!!

In Calif

In,
This would require a macro to be automatically run
just before the workbook is saved.
Is that a suitable?

Jim Cone
San Francisco, USA

"In Calif"
wrote in message
...
Hi Everybody,
Is there a way to dynamcally to protect a cell after the user's input upon
saving the file? What I am looking for is to prevent users from making
changes once the file is saved after their input. I am using this to track
vacation, personal day off & sick day dates and hours taken. The users are
given a date col & hrs col to input the date, I would like the input be
protected once the file is saved but the rest of the
empty cells in the col are still unprotected for future input.

Vac Date Vac Hrs.
1/23/2005 8 (Theses entries are protected after saving the file.)
2/23/2005 4
(Blank cells in the range are left
unprotected.)

Thanks for your time and help!!!

In Calif

Jim,

Thanks, how do you do that?

In Calif

"Jim Cone" wrote:

In,
This would require a macro to be automatically run
just before the workbook is saved.
Is that a suitable?

Jim Cone
San Francisco, USA

"In Calif"
wrote in message
...
Hi Everybody,
Is there a way to dynamcally to protect a cell after the user's input upon
saving the file? What I am looking for is to prevent users from making
changes once the file is saved after their input. I am using this to track
vacation, personal day off & sick day dates and hours taken. The users are
given a date col & hrs col to input the date, I would like the input be
protected once the file is saved but the rest of the
empty cells in the col are still unprotected for future input.

Vac Date Vac Hrs.
1/23/2005 8 (Theses entries are protected after saving the file.)
2/23/2005 4
(Blank cells in the range are left
unprotected.)

Thanks for your time and help!!!

In Calif

In,

You paste the following code in the "ThisWorkbook" module
of the workbook. You also make the noted changes/corrections
to the code after pasting.
Press Alt + F11 to get to the Visual Basic Editor (where the code goes).
Press Ctrl + R to view the project explorer (like windows explorer)
Look for your workbook project.
Expand it to show "ThisWorkbook".
Double click on that.

If you're new to macros, you may want to read:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Prudence dictates that you make a backup copy of your
workbook, before proceeding.

Jim Cone
San Francisco, USA

'------------------------------------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Jim Cone - San Francisco, USA - Sept. 13, 2005
'Assumes worksheet in question is named "Specify".
'Assumes Column "5" is the column for user input.
'Assumes the worksheet password is "Password"
'Unlocks cells in column 5 that are below the last entry in that column.
'Locks all cells in column 5 above (and including) the last entry.
'Protects the worksheet.

On Error GoTo SaveErr
Dim rngToProtect As Excel.Range
Dim rngBottom As Excel.Range
Application.EnableEvents = False
'REPLACE "SPECIFY" WITH CORRECT SHEET NAME
With ThisWorkbook.Worksheets("Specify")
'REPLACE "5" WITH CORRECT COLUMN NUMBER IN 3 PLACES.
Set rngBottom = .Cells(Rows.Count, 5).End(xlUp)
Set rngToProtect = .Range(.Cells(1, 5), rngBottom)
'REPLACE "PASSWORD" WITH CORRECT PASSWORD IN 2 PLACES.
.Unprotect "Password"
rngToProtect.Locked = True
.Range(rngBottom(2, 1), .Cells(Rows.Count, 5)).Locked = False
.Protect "Password"
End With

Set rngToProtect = Nothing
Set rngBottom = Nothing
Application.EnableEvents = True
Exit Sub

SaveErr:
MsgBox "Error " & Err.Number & " - " & Err.Description
Cancel = True
Application.EnableEvents = True
End Sub
'-----------------------------------------------


"In Calif"
wrote in message

Jim,
Thanks, how do you do that?
In Calif


"Jim Cone" wrote:
In,
This would require a macro to be automatically run
just before the workbook is saved.
Is that a suitable?
Jim Cone
San Francisco, USA

"In Calif"
wrote in message
...
Hi Everybody,
Is there a way to dynamcally to protect a cell after the user's input upon
saving the file? What I am looking for is to prevent users from making
changes once the file is saved after their input. I am using this to track
vacation, personal day off & sick day dates and hours taken. The users are
given a date col & hrs col to input the date, I would like the input be
protected once the file is saved but the rest of the
empty cells in the col are still unprotected for future input.
Vac Date Vac Hrs.
1/23/2005 8 (Theses entries are protected after saving the file.)
2/23/2005 4
(Blank cells in the range are left
unprotected.)
Thanks for your time and help!!!
In Calif

Thanks Jim, I give it a try!

In Calif

"Jim Cone" wrote:

In,

You paste the following code in the "ThisWorkbook" module
of the workbook. You also make the noted changes/corrections
to the code after pasting.
Press Alt + F11 to get to the Visual Basic Editor (where the code goes).
Press Ctrl + R to view the project explorer (like windows explorer)
Look for your workbook project.
Expand it to show "ThisWorkbook".
Double click on that.

If you're new to macros, you may want to read:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Prudence dictates that you make a backup copy of your
workbook, before proceeding.

Jim Cone
San Francisco, USA

'------------------------------------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Jim Cone - San Francisco, USA - Sept. 13, 2005
'Assumes worksheet in question is named "Specify".
'Assumes Column "5" is the column for user input.
'Assumes the worksheet password is "Password"
'Unlocks cells in column 5 that are below the last entry in that column.
'Locks all cells in column 5 above (and including) the last entry.
'Protects the worksheet.

On Error GoTo SaveErr
Dim rngToProtect As Excel.Range
Dim rngBottom As Excel.Range
Application.EnableEvents = False
'REPLACE "SPECIFY" WITH CORRECT SHEET NAME
With ThisWorkbook.Worksheets("Specify")
'REPLACE "5" WITH CORRECT COLUMN NUMBER IN 3 PLACES.
Set rngBottom = .Cells(Rows.Count, 5).End(xlUp)
Set rngToProtect = .Range(.Cells(1, 5), rngBottom)
'REPLACE "PASSWORD" WITH CORRECT PASSWORD IN 2 PLACES.
.Unprotect "Password"
rngToProtect.Locked = True
.Range(rngBottom(2, 1), .Cells(Rows.Count, 5)).Locked = False
.Protect "Password"
End With

Set rngToProtect = Nothing
Set rngBottom = Nothing
Application.EnableEvents = True
Exit Sub

SaveErr:
MsgBox "Error " & Err.Number & " - " & Err.Description
Cancel = True
Application.EnableEvents = True
End Sub
'-----------------------------------------------


"In Calif"
wrote in message

Jim,
Thanks, how do you do that?
In Calif


"Jim Cone" wrote:
In,
This would require a macro to be automatically run
just before the workbook is saved.
Is that a suitable?
Jim Cone
San Francisco, USA

"In Calif"
wrote in message
...
Hi Everybody,
Is there a way to dynamcally to protect a cell after the user's input upon
saving the file? What I am looking for is to prevent users from making
changes once the file is saved after their input. I am using this to track
vacation, personal day off & sick day dates and hours taken. The users are
given a date col & hrs col to input the date, I would like the input be
protected once the file is saved but the rest of the
empty cells in the col are still unprotected for future input.
Vac Date Vac Hrs.
1/23/2005 8 (Theses entries are protected after saving the file.)
2/23/2005 4
(Blank cells in the range are left
unprotected.)
Thanks for your time and help!!!
In Calif