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.

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
---

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.

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.