HasFormula protect cell

[SIGE]

Hello wizards,

Below sub works and does not work ...!!!
I mean: clicking on a cell that has a formula will "protect" the sheet
.... as you can unprotect the sheet. (Tools>Protection>Unprotect)
**But** does actually NOT protect at all as I can just overwrite the
formula in the cell!

Just try and see what I mean...

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.HasFormula = True Then
Me.Protect Password:=""
Else
Me.Unprotect Password:=""
End If
End Sub

Cheers Sige

 

[Vasant Nanavati]

Are the cells locked?

 

[Guest]

It works for me. Once I have entered a formula and I go back to the same
cell, I cannot overwrite the formula. Hope you have not unlocked the cells
and they are still locked?!

Alok Joshi

 

[Guest]

Is the target cell locked or unlocked? If it is unlocked then protecting the
sheet will have no effect. Try locking all of the formulas. Select the entire
sheet then...

F5 -> Special -> Formulas

This will select all of the formulas on the sheet. On the Cell format menu
select the protection tab and ensure the cells are locked.

HTH
Jim Thomlinson

 

[Guest]

One more thing that I forgot to mention. Make sure all of the cells you want
the user to be able to modify are unlocked. Otherwise if a user enters a
fromula into a cell that is locked, they will not be able to modify it later
on...

Jim Thomlinson

 

[SIGE]

Hi Guys,

You are all right... my target cells are unlocked!

But that is actually the whole point. I do NOT want to be locking all my
formula cells with: GoTo>Special>Formulas...

I would like this sub to do that automatically. Lock all Cells
containing formulas. I thought this would do it ...

Suggestions?

Sige

"NOSPAM" to be removed for direct mailing...

 

[SIGE]

This goes fine ...for my purposes!

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.HasFormula = True Then
Target.Locked = True
Me.Protect Password:=""
Else
Me.Unprotect Password:=""
End If
End Sub

Cheers Sige

"NOSPAM" to be removed for direct mailing...

 

[SIGE]

Nevertheless some cells get locked and others don't!
Any Ideas?

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
' On Error Resume Next
If Target.Locked = True And CheckBox1 = True Then
Me.Protect Password:=""
Else
If Target.HasFormula = True And CheckBox1 = True Then
On Error GoTo err:
Target.Locked = True '<<=*=*=*=*=*=*
Me.Protect Password:=""
Else
Me.Unprotect Password:=""
End If
End If
err:
End Sub


Sige

"NOSPAM" to be removed for direct mailing...