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. (ToolsProtectionUnprotect)
**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

Are the cells locked?

--

Vasant

"SIGE" wrote in message
om...
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. (ToolsProtectionUnprotect)
**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

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

"SIGE" wrote:

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. (ToolsProtectionUnprotect)
**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

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

"SIGE" wrote:

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. (ToolsProtectionUnprotect)
**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

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" wrote:

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. (ToolsProtectionUnprotect)
**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

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: GoToSpecialFormulas...

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...

*** Sent via Developersdex http://www.developersdex.com ***

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...

*** Sent via Developersdex http://www.developersdex.com ***

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...

*** Sent via Developersdex http://www.developersdex.com ***