| 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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loking Cells by Date
Put this code in your workbook_open event. This code will unlock all cells
with the date on your system and lock all other cells that have an earlier or later date. Option Explicit Private Sub Workbook_Open() 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 "Spike" wrote: 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 | |||
Linear Mode
