protecting and unprotecting

[Fusion1337]

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

 

[Don Guillett]

By default, all cells are LOCKED. Right click on the cell>format
cells>protection to see this. So, If you want all UN protected>select 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.

 

[Fusion1337]

By default, all cells are LOCKED. Right click on the cell>format
cells>protection to see this. So, If you want all UN protected>select 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

 

[Don Guillett]

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 tab>view code>copy/paste this>change pw to suit>Save
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
(e-mail address removed)

By default, all cells are LOCKED. Right click on the cell>format
cells>protection to see this. So, If you want all UN protected>select
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

 

[Fusion1337]

Thanks, that worked nicely

 

[Don Guillett]

Glad to help