protecting and unprotecting

  • Thread starter Thread starter Fusion1337
  • Start date Start date
F

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
 
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.
 
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.
Click to expand...

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
 
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.
Click to expand...

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
 
Back
Top