Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cutoff score for acceptance | Excel Worksheet Functions | |||
Text is cutoff in a cell | Excel Discussion (Misc queries) | |||
Y Axis Label is Cutoff | Charts and Charting in Excel | |||
Excel "Value (Y) Axis" is cutoff--Fix it! | Charts and Charting in Excel | |||
CUTOFF | Excel Discussion (Misc queries) |