Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
protecting and unprotecting
I googled the group before posting, but I wasn't able to make any use
of what I found (my VBA-fu is still pretty feeble). Anyway, I was wondering how to do the following: I would like to protect cell B1 if value in A1 is lesser than 10. thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
protecting and unprotecting
By default, all cells are LOCKED. Right click on the cellformat
cellsprotection to see this. So, If you want all UN protectedselect cells by selecting the box to the left of the column letters and above the row headers and unlock all then you can use code to lock the cell and then protect the sheet. -- Don Guillett Microsoft MVP Excel SalesAid Software "Fusion1337" wrote in message ... I googled the group before posting, but I wasn't able to make any use of what I found (my VBA-fu is still pretty feeble). Anyway, I was wondering how to do the following: I would like to protect cell B1 if value in A1 is lesser than 10. thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
protecting and unprotecting
On Sep 25, 7:59*pm, "Don Guillett" wrote:
By default, all cells are LOCKED. Right click on the cellformat cellsprotection to see this. So, If you want all UN protectedselect cells by selecting the box to the left of the column letters and above the row headers and unlock all then you can use code to lock the cell and then protect the sheet. Hey Don I'm sorry I phrased the question wrongly. Basically I would like to protect cell B1 from any kind of access or editing unless value in A1 is greater than 10. So presumably I would have to unlock A1 and protect the sheet, followed by the code to unlock B1 if the conditions in A1 are met. What would the code be? thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
protecting and unprotecting
When dealing with me, always answer at the TOP
Manually do as I suggested earlier to unlock a1 and protect the worksheet. Then, right click sheet tabview codecopy/paste thischange pw to suitSave the workbook Now, if a1 is 10 or more b1 will be unprotected. Private Sub Worksheet_Change(ByVal target As Range) If Intersect(target, Range("a1")) Is Nothing Then Exit Sub 'MsgBox "HI" If target 10 Then ActiveSheet.Unprotect Password:="pw" Range("b1").Locked = False ActiveSheet.Protect Password:="pw" Else ActiveSheet.Unprotect Password:="pw" Range("b1").Locked = True ActiveSheet.Protect Password:="pw" End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Fusion1337" wrote in message ... On Sep 25, 7:59 pm, "Don Guillett" wrote: By default, all cells are LOCKED. Right click on the cellformat cellsprotection to see this. So, If you want all UN protectedselect cells by selecting the box to the left of the column letters and above the row headers and unlock all then you can use code to lock the cell and then protect the sheet. Hey Don I'm sorry I phrased the question wrongly. Basically I would like to protect cell B1 from any kind of access or editing unless value in A1 is greater than 10. So presumably I would have to unlock A1 and protect the sheet, followed by the code to unlock B1 if the conditions in A1 are met. What would the code be? thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
protecting and unprotecting
Thanks, that worked nicely
Private Sub Worksheet_Change(ByVal target As Range) If Intersect(target, Range("a1")) Is Nothing Then Exit Sub 'MsgBox "HI" If target 10 Then ActiveSheet.Unprotect Password:="pw" Range("b1").Locked = False ActiveSheet.Protect Password:="pw" Else ActiveSheet.Unprotect Password:="pw" Range("b1").Locked = True ActiveSheet.Protect Password:="pw" End If End Sub |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
protecting and unprotecting
Glad to help
-- Don Guillett Microsoft MVP Excel SalesAid Software "Fusion1337" wrote in message ... Thanks, that worked nicely Private Sub Worksheet_Change(ByVal target As Range) If Intersect(target, Range("a1")) Is Nothing Then Exit Sub 'MsgBox "HI" If target 10 Then ActiveSheet.Unprotect Password:="pw" Range("b1").Locked = False ActiveSheet.Protect Password:="pw" Else ActiveSheet.Unprotect Password:="pw" Range("b1").Locked = True ActiveSheet.Protect Password:="pw" End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protecting and Unprotecting several worksheets at one time | Excel Discussion (Misc queries) | |||
Unprotecting a worksheet | Excel Worksheet Functions | |||
Macro for protecting and unprotecting multiple worksheets | Excel Discussion (Misc queries) | |||
Unprotecting a Sheet | Excel Discussion (Misc queries) | |||
PROTECTING/UNPROTECTING SHEETS | Excel Discussion (Misc queries) |