Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Protect After Saved
Dear experts,
I've a worksheet where a range A5:D100 is for some users to fill in information. Is there a way when the cell is blank, it can be input by any users. However, whenver an user fill in some information to some rows within the range, the non-blank cells will be protected by a password, say "tiptoe" after saved but other blank cells will still be unprotected and can be input by other users. Is there a VBA code can do this? If yes, please kindly advise the code. Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Protect After Saved
Select A5:D100 and set properties to "unlocked". All other cells set to
"locked" Right-click on the sheet tab and "View Code". Copy/paste this into the module that opens up. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Not Intersect(Target, Me.Range("A5:D100")) Is Nothing Then ActiveSheet.Unprotect Password:="tiptoe" If Target.Value < "" Then Target.Locked = True End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="tiptoe" End Sub Alt + q to return to the Excel sheet. As you enter data in A5:D100 the cells will become locked. Gord Dibben MS Excel MVP On Thu, 19 Jun 2008 23:06:01 -0700, Freshman wrote: Dear experts, I've a worksheet where a range A5:D100 is for some users to fill in information. Is there a way when the cell is blank, it can be input by any users. However, whenver an user fill in some information to some rows within the range, the non-blank cells will be protected by a password, say "tiptoe" after saved but other blank cells will still be unprotected and can be input by other users. Is there a VBA code can do this? If yes, please kindly advise the code. Thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Protect After Saved
Dear Gord,
Thanks for your time to write me the code. I'm really appreciated it. Have a nice weekend. Best regard to you and your family. "Gord Dibben" wrote: Select A5:D100 and set properties to "unlocked". All other cells set to "locked" Right-click on the sheet tab and "View Code". Copy/paste this into the module that opens up. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Not Intersect(Target, Me.Range("A5:D100")) Is Nothing Then ActiveSheet.Unprotect Password:="tiptoe" If Target.Value < "" Then Target.Locked = True End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="tiptoe" End Sub Alt + q to return to the Excel sheet. As you enter data in A5:D100 the cells will become locked. Gord Dibben MS Excel MVP On Thu, 19 Jun 2008 23:06:01 -0700, Freshman wrote: Dear experts, I've a worksheet where a range A5:D100 is for some users to fill in information. Is there a way when the cell is blank, it can be input by any users. However, whenver an user fill in some information to some rows within the range, the non-blank cells will be protected by a password, say "tiptoe" after saved but other blank cells will still be unprotected and can be input by other users. Is there a VBA code can do this? If yes, please kindly advise the code. Thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Protect After Saved
Thanks for the feedback and we will have a nice weekend.
Gord On Sat, 21 Jun 2008 06:05:00 -0700, Freshman wrote: Dear Gord, Thanks for your time to write me the code. I'm really appreciated it. Have a nice weekend. Best regard to you and your family. "Gord Dibben" wrote: Select A5:D100 and set properties to "unlocked". All other cells set to "locked" Right-click on the sheet tab and "View Code". Copy/paste this into the module that opens up. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Not Intersect(Target, Me.Range("A5:D100")) Is Nothing Then ActiveSheet.Unprotect Password:="tiptoe" If Target.Value < "" Then Target.Locked = True End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="tiptoe" End Sub Alt + q to return to the Excel sheet. As you enter data in A5:D100 the cells will become locked. Gord Dibben MS Excel MVP On Thu, 19 Jun 2008 23:06:01 -0700, Freshman wrote: Dear experts, I've a worksheet where a range A5:D100 is for some users to fill in information. Is there a way when the cell is blank, it can be input by any users. However, whenver an user fill in some information to some rows within the range, the non-blank cells will be protected by a password, say "tiptoe" after saved but other blank cells will still be unprotected and can be input by other users. Is there a VBA code can do this? If yes, please kindly advise the code. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect spreadsheet from being saved | Excel Discussion (Misc queries) | |||
Protect Workbook Vs Protect Sheet | New Users to Excel | |||
Password Protect When Saved | Excel Discussion (Misc queries) | |||
How can I see a copy of a saved workbook before I saved it again? | Excel Worksheet Functions | |||
How to get saved old saved work that was saved over? | Excel Discussion (Misc queries) |