| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loking Cells by Date
I have a chart where information is filled in every day. I would like the
cells to be available only "today." How do I have the cells lock for all days that have passed, aas well as all of the days in the future? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loking Cells by Date
This should work for you! I took the liberty of highlighting the unlocked
cells yellow. This will maximize readilbilty. If you don't want that then just delete it out of the code. Note: The sheet has to be protected for the Lock Property of the cells to be active. Sub LockDates() Const wksName As String = "Sheet1" Dim cell As Range Sheets(wksName).Unprotect Password:="" For Each cell In Sheets(wksName).UsedRange If cell.Value = Date Then cell.Locked = False Else cell.Locked = True End If Next cell Sheets(wksName).Protect Password:="" End Sub If this helps please click "Yes" below. -- Cheers, Ryan "Daniel" wrote: I have a chart where information is filled in every day. I would like the cells to be available only "today." How do I have the cells lock for all days that have passed, aas well as all of the days in the future? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loking Cells by Date
Oops! I forgot the hightlight part.
Sub LockDates() Const wksName As String = "Sheet1" Dim cell As Range Sheets(wksName).Unprotect Password:="" For Each cell In Sheets(wksName).UsedRange With cell If .Value = Date Then .Locked = False .Interior.Color = 3 Else .Locked = True .Interior.Color = xlNone End If End With Next cell Sheets(wksName).Protect Password:="" End Sub -- Cheers, Ryan "RyanH" wrote: This should work for you! I took the liberty of highlighting the unlocked cells yellow. This will maximize readilbilty. If you don't want that then just delete it out of the code. Note: The sheet has to be protected for the Lock Property of the cells to be active. Sub LockDates() Const wksName As String = "Sheet1" Dim cell As Range Sheets(wksName).Unprotect Password:="" For Each cell In Sheets(wksName).UsedRange If cell.Value = Date Then cell.Locked = False Else cell.Locked = True End If Next cell Sheets(wksName).Protect Password:="" End Sub If this helps please click "Yes" below. -- Cheers, Ryan "Daniel" wrote: I have a chart where information is filled in every day. I would like the cells to be available only "today." How do I have the cells lock for all days that have passed, aas well as all of the days in the future? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loking Cells by Date
Ryan, I had put in that code, but I could still not get it to work. I was
wondering if you had a minute to look at my worksheet that the original post was for? "RyanH" wrote: Oops! I forgot the hightlight part. Sub LockDates() Const wksName As String = "Sheet1" Dim cell As Range Sheets(wksName).Unprotect Password:="" For Each cell In Sheets(wksName).UsedRange With cell If .Value = Date Then .Locked = False .Interior.Color = 3 Else .Locked = True .Interior.Color = xlNone End If End With Next cell Sheets(wksName).Protect Password:="" End Sub -- Cheers, Ryan "RyanH" wrote: This should work for you! I took the liberty of highlighting the unlocked cells yellow. This will maximize readilbilty. If you don't want that then just delete it out of the code. Note: The sheet has to be protected for the Lock Property of the cells to be active. Sub LockDates() Const wksName As String = "Sheet1" Dim cell As Range Sheets(wksName).Unprotect Password:="" For Each cell In Sheets(wksName).UsedRange If cell.Value = Date Then cell.Locked = False Else cell.Locked = True End If Next cell Sheets(wksName).Protect Password:="" End Sub If this helps please click "Yes" below. -- Cheers, Ryan "Daniel" wrote: I have a chart where information is filled in every day. I would like the cells to be available only "today." How do I have the cells lock for all days that have passed, aas well as all of the days in the future? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loking Cells by Date
What did not work? Please explain. Did you get an error? If so, indicate
where in the code. -- Cheers, Ryan "Spike" wrote: Ryan, I had put in that code, but I could still not get it to work. I was wondering if you had a minute to look at my worksheet that the original post was for? "RyanH" wrote: Oops! I forgot the hightlight part. Sub LockDates() Const wksName As String = "Sheet1" Dim cell As Range Sheets(wksName).Unprotect Password:="" For Each cell In Sheets(wksName).UsedRange With cell If .Value = Date Then .Locked = False .Interior.Color = 3 Else .Locked = True .Interior.Color = xlNone End If End With Next cell Sheets(wksName).Protect Password:="" End Sub -- Cheers, Ryan "RyanH" wrote: This should work for you! I took the liberty of highlighting the unlocked cells yellow. This will maximize readilbilty. If you don't want that then just delete it out of the code. Note: The sheet has to be protected for the Lock Property of the cells to be active. Sub LockDates() Const wksName As String = "Sheet1" Dim cell As Range Sheets(wksName).Unprotect Password:="" For Each cell In Sheets(wksName).UsedRange If cell.Value = Date Then cell.Locked = False Else cell.Locked = True End If Next cell Sheets(wksName).Protect Password:="" End Sub If this helps please click "Yes" below. -- Cheers, Ryan "Daniel" wrote: I have a chart where information is filled in every day. I would like the cells to be available only "today." How do I have the cells lock for all days that have passed, aas well as all of the days in the future? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loking Cells by Date
I put in that code that you had said, but nothing happened. I don't know if
i just don't remember if I need to change any of the code, but nothing happened when I put in the code "RyanH" wrote: What did not work? Please explain. Did you get an error? If so, indicate where in the code. -- Cheers, Ryan "Spike" wrote: Ryan, I had put in that code, but I could still not get it to work. I was wondering if you had a minute to look at my worksheet that the original post was for? "RyanH" wrote: Oops! I forgot the hightlight part. Sub LockDates() Const wksName As String = "Sheet1" Dim cell As Range Sheets(wksName).Unprotect Password:="" For Each cell In Sheets(wksName).UsedRange With cell If .Value = Date Then .Locked = False .Interior.Color = 3 Else .Locked = True .Interior.Color = xlNone End If End With Next cell Sheets(wksName).Protect Password:="" End Sub -- Cheers, Ryan "RyanH" wrote: This should work for you! I took the liberty of highlighting the unlocked cells yellow. This will maximize readilbilty. If you don't want that then just delete it out of the code. Note: The sheet has to be protected for the Lock Property of the cells to be active. Sub LockDates() Const wksName As String = "Sheet1" Dim cell As Range Sheets(wksName).Unprotect Password:="" For Each cell In Sheets(wksName).UsedRange If cell.Value = Date Then cell.Locked = False Else cell.Locked = True End If Next cell Sheets(wksName).Protect Password:="" End Sub If this helps please click "Yes" below. -- Cheers, Ryan "Daniel" wrote: I have a chart where information is filled in every day. I would like the cells to be available only "today." How do I have the cells lock for all days that have passed, aas well as all of the days in the future? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Clarification - how to automatically update cells with a date and date range | Excel Discussion (Misc queries) | |||
| Split date from date time cells dd/mm/yyyy hh:mm. New cell dd/mm/y | Excel Discussion (Misc queries) | |||
| Automatically update a cell with a date based on anther cells date | Excel Discussion (Misc queries) | |||
| About loking cells.. | Excel Worksheet Functions | |||
| Cell Blink or message if cells date = today date | Excel Programming | |||
Hybrid Mode
