Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formating using dates
I have data that is entered from a report that is done periodically and dont
want users to use the spreadsheet after a specific date (this date is entered into a header). So I want to be able to look at the current date and compare it to the header date and if the current date exceeds the header date then the spreadsheet becomes unusable. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formating using dates
"jorgie" wrote:
I have data that is entered from a report that is done periodically and dont want users to use the spreadsheet after a specific date (this date is entered into a header). So I want to be able to look at the current date and compare it to the header date and if the current date exceeds the header date then the spreadsheet becomes unusable. No answer, just some thoughts. Don't think CF* can render this: the spreadsheet becomes unusable *going by your subject line <g Hang around for better insights .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formating using dates
Try this in VBA (Tools-Macro-Visual Basic Editor then select required sheet)
Assuming your date for validation is in A1. Copy the following code. ========================================= Private Sub Worksheet_Change(ByVal Target As Range) If Range("A1") = Date Then With Cells.Validation .Delete .Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _ Operator:=xlEqual, Formula1:="0" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "Sheet Expired" .InputMessage = "" .ErrorMessage = "Sheet Expired" .ShowInput = True .ShowError = True End With End If End Sub ========================================= Please rate me. "jorgie" wrote: I have data that is entered from a report that is done periodically and dont want users to use the spreadsheet after a specific date (this date is entered into a header). So I want to be able to look at the current date and compare it to the header date and if the current date exceeds the header date then the spreadsheet becomes unusable. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formating using dates
If you delete the date in cell A1 the code still "locks" the sheet. Also,
how does the owner of the file "unlock" it? Biff "Naveen" wrote in message ... Try this in VBA (Tools-Macro-Visual Basic Editor then select required sheet) Assuming your date for validation is in A1. Copy the following code. ========================================= Private Sub Worksheet_Change(ByVal Target As Range) If Range("A1") = Date Then With Cells.Validation .Delete .Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _ Operator:=xlEqual, Formula1:="0" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "Sheet Expired" .InputMessage = "" .ErrorMessage = "Sheet Expired" .ShowInput = True .ShowError = True End With End If End Sub ========================================= Please rate me. "jorgie" wrote: I have data that is entered from a report that is done periodically and don't want users to use the spreadsheet after a specific date (this date is entered into a header). So I want to be able to look at the current date and compare it to the header date and if the current date exceeds the header date then the spreadsheet becomes unusable. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dates in Conditional Formatting help!!! | Excel Worksheet Functions | |||
can i freeze conditional formating to copy to another worksheet | Excel Discussion (Misc queries) | |||
IF Command conditional formating | Excel Worksheet Functions | |||
Conditional Formating & Rand() function | Excel Discussion (Misc queries) | |||
conditional formating using cells containg dates | Excel Discussion (Misc queries) |