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

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

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

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

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

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