Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking worksheet based on the cell value
I have a workbook containing 14 worksheets that are used as daily shift
report for each day of the biwekly pay period. Supervisors fill out reports on a daily basis and managers review them every couple of days. I'd like to limit user's ability to filling out today's report only (maybe a code that would compare the date on the report with cell that contains =TODAY() function, and locks the worksheet for editing if those two values don't mach). The idea is not to allow anyone to go back and edit reports. Any help on this would be greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking worksheet based on the cell value
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' Protect sheet with a password if sheet not ' valid for today ' Date of sheet is assumed to be in cell A1. Password is "Pass" ' Since this may be a file which is left open permanently, then ' this routine might be placed under ' This workbook / workbook / sheet change Dim NShts As Integer, i1 As Integer With ActiveWorkbook NShts = .Sheets.Count For i1 = 1 To NShts If .Sheets(i1).Range("A1").Value < Date Then ActiveWorkbook.Sheets(i1).Protect "Pass" End If Next i1 End With End Sub "0o0o0o0o" wrote: I have a workbook containing 14 worksheets that are used as daily shift report for each day of the biwekly pay period. Supervisors fill out reports on a daily basis and managers review them every couple of days. I'd like to limit user's ability to filling out today's report only (maybe a code that would compare the date on the report with cell that contains =TODAY() function, and locks the worksheet for editing if those two values don't mach). The idea is not to allow anyone to go back and edit reports. Any help on this would be greatly appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking worksheet based on the cell value
Thank you for your help, I tried your code and it works fine, except in some
of the sheets that have a date other than today's, it allows user to enter value in one cell, and then locks the sheet. Another problem: each worksheet contains bunch of cells with formulas, allowing users to edit cells with formulas. Most of the people that will be using this workbook have limited knowledge of Excel so I'm afraid they will delete some formulas (by mistake of course). I tried editing your code to hide the worksheet if A1 cell is not equal to today's date, but it's not working. Any ideas? Thank you again. "Gleam" wrote: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) ' Protect sheet with a password if sheet not ' valid for today ' Date of sheet is assumed to be in cell A1. Password is "Pass" ' Since this may be a file which is left open permanently, then ' this routine might be placed under ' This workbook / workbook / sheet change Dim NShts As Integer, i1 As Integer With ActiveWorkbook NShts = .Sheets.Count For i1 = 1 To NShts If .Sheets(i1).Range("A1").Value < Date Then ActiveWorkbook.Sheets(i1).Protect "Pass" End If Next i1 End With End Sub "0o0o0o0o" wrote: I have a workbook containing 14 worksheets that are used as daily shift report for each day of the biwekly pay period. Supervisors fill out reports on a daily basis and managers review them every couple of days. I'd like to limit user's ability to filling out today's report only (maybe a code that would compare the date on the report with cell that contains =TODAY() function, and locks the worksheet for editing if those two values don't mach). The idea is not to allow anyone to go back and edit reports. Any help on this would be greatly appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking worksheet based on the cell value
I suggest moving the code to a module and then calling the code for both
sheet activate and sheet change. If there is no risk of the file being left open after midnight then sheet change is not requried. I have alos modified the code so thatthe user cells are unlocked on today's sheet but locked on the other days. Sub ProtectSht() ' Protect sheet with a password if sheet not ' valid for today ' Date of sheet is assumed to be in cell A1 ' Since this may be a file which is left open permanently, then ' this routine should be placed in a module and then ' This workbook / workbook / sheet activate should call this routine ' If the file is likely to be left open past midnight then ' This workbook / workbook / sheet change should call this routine. ' This will allow just one change to be made after midnight to ' the currently selected cell. Dim NShts As Integer, i1 As Integer Dim UA1 As Range, UA2 As Range, UA3 As Range, UAT As Range With ActiveWorkbook NShts = .Sheets.Count For i1 = 1 To NShts ' Set user areas - users can enter data in these cells Set UA1 = .Sheets(i1).Range("B5:B12") ' User Area 1 Set UA2 = .Sheets(i1).Range("F5:G12") ' User Area 2 Set UA3 = .Sheets(i1).Range("A14:B17") 'User Area 3 Set UAT = Union(UA1, UA2, UA3) If .Sheets(i1).Range("A1").Value < Date Then .Sheets(i1).Unprotect "Pass" UAT.Locked = True .Sheets(i1).Protect "Pass" Else .Sheets(i1).Unprotect "Pass" UAT.Locked = False .Sheets(i1).Protect "Pass" End If Next i1 End With End Sub "0o0o0o0o" wrote: Thank you for your help, I tried your code and it works fine, except in some of the sheets that have a date other than today's, it allows user to enter value in one cell, and then locks the sheet. Another problem: each worksheet contains bunch of cells with formulas, allowing users to edit cells with formulas. Most of the people that will be using this workbook have limited knowledge of Excel so I'm afraid they will delete some formulas (by mistake of course). I tried editing your code to hide the worksheet if A1 cell is not equal to today's date, but it's not working. Any ideas? Thank you again. "Gleam" wrote: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) ' Protect sheet with a password if sheet not ' valid for today ' Date of sheet is assumed to be in cell A1. Password is "Pass" ' Since this may be a file which is left open permanently, then ' this routine might be placed under ' This workbook / workbook / sheet change Dim NShts As Integer, i1 As Integer With ActiveWorkbook NShts = .Sheets.Count For i1 = 1 To NShts If .Sheets(i1).Range("A1").Value < Date Then ActiveWorkbook.Sheets(i1).Protect "Pass" End If Next i1 End With End Sub "0o0o0o0o" wrote: I have a workbook containing 14 worksheets that are used as daily shift report for each day of the biwekly pay period. Supervisors fill out reports on a daily basis and managers review them every couple of days. I'd like to limit user's ability to filling out today's report only (maybe a code that would compare the date on the report with cell that contains =TODAY() function, and locks the worksheet for editing if those two values don't mach). The idea is not to allow anyone to go back and edit reports. Any help on this would be greatly appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking worksheet based on the cell value
Just seen the bit about hiding sheets: please try this (it hides the sheets
and protects the workbook to prevent users unhidng them): (I use tools / macro / record macro to get the basis of the code in many cases) Sub ProtectSht() ' Protect sheet with a password if sheet not ' valid for today ' Date of sheet is assumed to be in cell A1 ' Since this may be a file which is left open permanently, then ' this routine should be placed in a module and then ' This workbook / workbook / sheet activate should call this routine ' If the file is likely to be left open past midnight then ' This workbook / workbook / sheet change should call this routine. ' This will allow just one change to be made after midnight to ' the currently selected cell. Dim NShts As Integer, i1 As Integer Dim UA1 As Range, UA2 As Range, UA3 As Range, UAT As Range With ActiveWorkbook .Unprotect "Pass" NShts = .Sheets.Count For i1 = 1 To NShts ' Set user areas - users can enter data in these cells Set UA1 = .Sheets(i1).Range("B5:B12") ' User Area 1 Set UA2 = .Sheets(i1).Range("F5:G12") ' User Area 2 Set UA3 = .Sheets(i1).Range("A14:B17") 'User Area 3 Set UAT = Union(UA1, UA2, UA3) If .Sheets(i1).Range("A1").Value < Date Then .Sheets(i1).Unprotect "Pass" UAT.Locked = True .Sheets(i1).Protect "Pass" .Sheets(i1).Visible = False Else .Sheets(i1).Unprotect "Pass" UAT.Locked = False .Sheets(i1).Protect "Pass" .Sheets(i1).Visible = True End If Next i1 .Protect Password:="Pass", Structu=True, Windows:=False End With End Sub "0o0o0o0o" wrote: Thank you for your help, I tried your code and it works fine, except in some of the sheets that have a date other than today's, it allows user to enter value in one cell, and then locks the sheet. Another problem: each worksheet contains bunch of cells with formulas, allowing users to edit cells with formulas. Most of the people that will be using this workbook have limited knowledge of Excel so I'm afraid they will delete some formulas (by mistake of course). I tried editing your code to hide the worksheet if A1 cell is not equal to today's date, but it's not working. Any ideas? Thank you again. "Gleam" wrote: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) ' Protect sheet with a password if sheet not ' valid for today ' Date of sheet is assumed to be in cell A1. Password is "Pass" ' Since this may be a file which is left open permanently, then ' this routine might be placed under ' This workbook / workbook / sheet change Dim NShts As Integer, i1 As Integer With ActiveWorkbook NShts = .Sheets.Count For i1 = 1 To NShts If .Sheets(i1).Range("A1").Value < Date Then ActiveWorkbook.Sheets(i1).Protect "Pass" End If Next i1 End With End Sub "0o0o0o0o" wrote: I have a workbook containing 14 worksheets that are used as daily shift report for each day of the biwekly pay period. Supervisors fill out reports on a daily basis and managers review them every couple of days. I'd like to limit user's ability to filling out today's report only (maybe a code that would compare the date on the report with cell that contains =TODAY() function, and locks the worksheet for editing if those two values don't mach). The idea is not to allow anyone to go back and edit reports. Any help on this would be greatly appreciated. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking worksheet based on the cell value
I have just read the bit about hiding sheets. I think you need to have at
least two sheets open, otherwise the user has to change a cell to get the macros to run. With this method the user selects another sheet and the macro runs. Sub ProtectSht() ' Protect sheet with a password if sheet not ' valid for today ' Date of sheet is assumed to be in cell A1 ' Since this may be a file which is left open permanently, then ' this routine should be placed in a module and then ' This workbook / workbook / sheet activate should call this routine ' If the file is likely to be left open past midnight then ' This workbook / workbook / sheet change should call this routine. ' This will allow just one change to be made after midnight to ' the currently selected cell. Dim NShts As Integer, i1 As Integer Dim UA1 As Range, UA2 As Range, UA3 As Range, UAT As Range With ActiveWorkbook .Unprotect "Pass" NShts = .Sheets.Count For i1 = 1 To NShts ' Set user areas - users can enter data in these cells Set UA1 = .Sheets(i1).Range("B5:B12") ' User Area 1 Set UA2 = .Sheets(i1).Range("F5:G12") ' User Area 2 Set UA3 = .Sheets(i1).Range("A14:B17") 'User Area 3 Set UAT = Union(UA1, UA2, UA3) .Sheets(i1).Unprotect "Pass" With UAT.Interior ' colour user cells yellow .ColorIndex = 6 .Pattern = xlSolid End With If .Sheets(i1).Range("A1").Value < Date Then UAT.Locked = True If .Sheets(i1).Range("A1").Value < Date + 1 Then .Sheets(i1).Visible = False Else .Sheets(i1).Visible = True End If Else UAT.Locked = False .Sheets(i1).Visible = True End If .Sheets(i1).Protect "Pass" Next i1 .Protect Password:="Pass", Structu=True, Windows:=False End With End Sub "0o0o0o0o" wrote: Thank you for your help, I tried your code and it works fine, except in some of the sheets that have a date other than today's, it allows user to enter value in one cell, and then locks the sheet. Another problem: each worksheet contains bunch of cells with formulas, allowing users to edit cells with formulas. Most of the people that will be using this workbook have limited knowledge of Excel so I'm afraid they will delete some formulas (by mistake of course). I tried editing your code to hide the worksheet if A1 cell is not equal to today's date, but it's not working. Any ideas? Thank you again. "Gleam" wrote: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) ' Protect sheet with a password if sheet not ' valid for today ' Date of sheet is assumed to be in cell A1. Password is "Pass" ' Since this may be a file which is left open permanently, then ' this routine might be placed under ' This workbook / workbook / sheet change Dim NShts As Integer, i1 As Integer With ActiveWorkbook NShts = .Sheets.Count For i1 = 1 To NShts If .Sheets(i1).Range("A1").Value < Date Then ActiveWorkbook.Sheets(i1).Protect "Pass" End If Next i1 End With End Sub "0o0o0o0o" wrote: I have a workbook containing 14 worksheets that are used as daily shift report for each day of the biwekly pay period. Supervisors fill out reports on a daily basis and managers review them every couple of days. I'd like to limit user's ability to filling out today's report only (maybe a code that would compare the date on the report with cell that contains =TODAY() function, and locks the worksheet for editing if those two values don't mach). The idea is not to allow anyone to go back and edit reports. Any help on this would be greatly appreciated. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Locking worksheet based on the cell value
Thank you very much, seems like you put some time in this.
I'm going to try this code and let you know how it's going. Thanks again. "Gleam" wrote: I have just read the bit about hiding sheets. I think you need to have at least two sheets open, otherwise the user has to change a cell to get the macros to run. With this method the user selects another sheet and the macro runs. Sub ProtectSht() ' Protect sheet with a password if sheet not ' valid for today ' Date of sheet is assumed to be in cell A1 ' Since this may be a file which is left open permanently, then ' this routine should be placed in a module and then ' This workbook / workbook / sheet activate should call this routine ' If the file is likely to be left open past midnight then ' This workbook / workbook / sheet change should call this routine. ' This will allow just one change to be made after midnight to ' the currently selected cell. Dim NShts As Integer, i1 As Integer Dim UA1 As Range, UA2 As Range, UA3 As Range, UAT As Range With ActiveWorkbook .Unprotect "Pass" NShts = .Sheets.Count For i1 = 1 To NShts ' Set user areas - users can enter data in these cells Set UA1 = .Sheets(i1).Range("B5:B12") ' User Area 1 Set UA2 = .Sheets(i1).Range("F5:G12") ' User Area 2 Set UA3 = .Sheets(i1).Range("A14:B17") 'User Area 3 Set UAT = Union(UA1, UA2, UA3) .Sheets(i1).Unprotect "Pass" With UAT.Interior ' colour user cells yellow .ColorIndex = 6 .Pattern = xlSolid End With If .Sheets(i1).Range("A1").Value < Date Then UAT.Locked = True If .Sheets(i1).Range("A1").Value < Date + 1 Then .Sheets(i1).Visible = False Else .Sheets(i1).Visible = True End If Else UAT.Locked = False .Sheets(i1).Visible = True End If .Sheets(i1).Protect "Pass" Next i1 .Protect Password:="Pass", Structu=True, Windows:=False End With End Sub "0o0o0o0o" wrote: Thank you for your help, I tried your code and it works fine, except in some of the sheets that have a date other than today's, it allows user to enter value in one cell, and then locks the sheet. Another problem: each worksheet contains bunch of cells with formulas, allowing users to edit cells with formulas. Most of the people that will be using this workbook have limited knowledge of Excel so I'm afraid they will delete some formulas (by mistake of course). I tried editing your code to hide the worksheet if A1 cell is not equal to today's date, but it's not working. Any ideas? Thank you again. "Gleam" wrote: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) ' Protect sheet with a password if sheet not ' valid for today ' Date of sheet is assumed to be in cell A1. Password is "Pass" ' Since this may be a file which is left open permanently, then ' this routine might be placed under ' This workbook / workbook / sheet change Dim NShts As Integer, i1 As Integer With ActiveWorkbook NShts = .Sheets.Count For i1 = 1 To NShts If .Sheets(i1).Range("A1").Value < Date Then ActiveWorkbook.Sheets(i1).Protect "Pass" End If Next i1 End With End Sub "0o0o0o0o" wrote: I have a workbook containing 14 worksheets that are used as daily shift report for each day of the biwekly pay period. Supervisors fill out reports on a daily basis and managers review them every couple of days. I'd like to limit user's ability to filling out today's report only (maybe a code that would compare the date on the report with cell that contains =TODAY() function, and locks the worksheet for editing if those two values don't mach). The idea is not to allow anyone to go back and edit reports. Any help on this would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locking a cell based on a cells content | Excel Discussion (Misc queries) | |||
Cell locking based on domain user | Excel Worksheet Functions | |||
Locking/Unlocking based on another cell value | Excel Worksheet Functions | |||
Excel Locking cells based on value in another cell | Excel Discussion (Misc queries) | |||
Locking other cells based on values in a cell. | Excel Programming |