Cutoff Time for Changes
 

Is it possible to command MS Excel to stop accepting updates in a spreadsheet
after a certain date and time? We plan to post it as a custom list in
SharePoint. We want to say that after a certain date only a couple of
individuals will be authorized to make changes - (in other words, one week
out from the appointment date the spreadsheet will close except to the
owners). How would we accomplish this?

DOUG

Cutoff Time for Changes
 

Note that although you can apply a password to Excel as you ask, the
passwords are notoriously week. Internal passwords can be cracked with macros
easily available online, and external ones can be cracked through other
applications.

However, assuming that you're dealing with honest people who are more easily
stopped, something like this could work:

Open up the VBA editor (Alt+F11) and open the ThisWorkbook module. Paste
this in:

Private Sub Workbook_Open()
'Change date to what you want
'Note that year must be 4 digits
If DateValue(Now) = "5/5/2009" Then

'Change name of sheet as appropriate
Sheets("Sheet1").select

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True,
Password:="MyPassword"
'Change MyPassword to whatever you want it to be

End If
End Sub

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"DOUG" wrote:

Is it possible to command MS Excel to stop accepting updates in a spreadsheet
after a certain date and time? We plan to post it as a custom list in
SharePoint. We want to say that after a certain date only a couple of
individuals will be authorized to make changes - (in other words, one week
out from the appointment date the spreadsheet will close except to the
owners). How would we accomplish this?

DOUG

Cutoff Time for Changes
 

Luke: In your response, are the sentences delineated by single quotation
marks you talking to me or is that part of the instruction to enter in VBA?

DOUG

PS, and THANK YOU, by the way.

"Luke M" wrote:

Note that although you can apply a password to Excel as you ask, the
passwords are notoriously week. Internal passwords can be cracked with macros
easily available online, and external ones can be cracked through other
applications.

However, assuming that you're dealing with honest people who are more easily
stopped, something like this could work:

Open up the VBA editor (Alt+F11) and open the ThisWorkbook module. Paste
this in:

Private Sub Workbook_Open()
'Change date to what you want
'Note that year must be 4 digits
If DateValue(Now) = "5/5/2009" Then

'Change name of sheet as appropriate
Sheets("Sheet1").select

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True,
Password:="MyPassword"
'Change MyPassword to whatever you want it to be

End If
End Sub

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"DOUG" wrote:

Is it possible to command MS Excel to stop accepting updates in a spreadsheet
after a certain date and time? We plan to post it as a custom list in
SharePoint. We want to say that after a certain date only a couple of
individuals will be authorized to make changes - (in other words, one week
out from the appointment date the spreadsheet will close except to the
owners). How would we accomplish this?

DOUG

Cutoff Time for Changes
 

And if they don't enable the macros then??? The spreadsheet is wide open.

My take would be to just put a password on the file on the appropriate date.
When the user opens the file they get a read only copy unless they have the
password.
File - Save As - Tools - Protection (that is also a relatively robust
password that the average user will not be able to hack).
--
HTH...

Jim Thomlinson


"Luke M" wrote:

Note that although you can apply a password to Excel as you ask, the
passwords are notoriously week. Internal passwords can be cracked with macros
easily available online, and external ones can be cracked through other
applications.

However, assuming that you're dealing with honest people who are more easily
stopped, something like this could work:

Open up the VBA editor (Alt+F11) and open the ThisWorkbook module. Paste
this in:

Private Sub Workbook_Open()
'Change date to what you want
'Note that year must be 4 digits
If DateValue(Now) = "5/5/2009" Then

'Change name of sheet as appropriate
Sheets("Sheet1").select

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True,
Password:="MyPassword"
'Change MyPassword to whatever you want it to be

End If
End Sub

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"DOUG" wrote:

Is it possible to command MS Excel to stop accepting updates in a spreadsheet
after a certain date and time? We plan to post it as a custom list in
SharePoint. We want to say that after a certain date only a couple of
individuals will be authorized to make changes - (in other words, one week
out from the appointment date the spreadsheet will close except to the
owners). How would we accomplish this?

DOUG

Cutoff Time for Changes
 

Jim: Thank you. I suspect that is what I shall do.

Luke: Where is ThisWorkbookModule?

DOUG

"Jim Thomlinson" wrote:

And if they don't enable the macros then??? The spreadsheet is wide open.

My take would be to just put a password on the file on the appropriate date.
When the user opens the file they get a read only copy unless they have the
password.
File - Save As - Tools - Protection (that is also a relatively robust
password that the average user will not be able to hack).
--
HTH...

Jim Thomlinson


"Luke M" wrote:

Note that although you can apply a password to Excel as you ask, the
passwords are notoriously week. Internal passwords can be cracked with macros
easily available online, and external ones can be cracked through other
applications.

However, assuming that you're dealing with honest people who are more easily
stopped, something like this could work:

Open up the VBA editor (Alt+F11) and open the ThisWorkbook module. Paste
this in:

Private Sub Workbook_Open()
'Change date to what you want
'Note that year must be 4 digits
If DateValue(Now) = "5/5/2009" Then

'Change name of sheet as appropriate
Sheets("Sheet1").select

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True,
Password:="MyPassword"
'Change MyPassword to whatever you want it to be

End If
End Sub

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"DOUG" wrote:

Is it possible to command MS Excel to stop accepting updates in a spreadsheet
after a certain date and time? We plan to post it as a custom list in
SharePoint. We want to say that after a certain date only a couple of
individuals will be authorized to make changes - (in other words, one week
out from the appointment date the spreadsheet will close except to the
owners). How would we accomplish this?

DOUG