Protect formula on worksheet but allow changes to the worksheet

[Kurt]

Hello,

What is the best excel vba formula to use in Excel 2003 to protect formulas in a worksheet from being overwritten, but allowing changes to other parts of the spreadsheet it may reference?

 

[jproffer]

What I think you mean is this:

Sub lock_formulas()
Set FormulaCells = ActiveSheet.Range("A1").SpecialCells(xlFormulas, xlNumbers + xlTextValues + xlLogical)
    If Not IsEmpty(FormulaCells) Then
        For Each Area In FormulaCells.Areas
            With ActiveSheet.Range(Area.Address)
                .Locked = True
            End With
        Next Area
    End If

End Sub

If that's not what you want to do, please explain further.

 

[Kurt]

I will give it a try to I place this in the User Form Initialize event?

 

[jproffer]

I thought this was on a worksheet.

As in, you had a worksheet with 10000's (or however many )of formulas on it and you didn't want to search thru and lock each cell with a formula manually.

We need more details about what you're doing. What it does now...what you want it to do...when should it protect a cell with a formula (once or every time a formula is entered??) What does the userform do? What is overwriting your formulas that you need them to be locked...and why is it overwriting them?

Any code you already have that's relevant to what you're trying to accomplish would be helpful.

 

[Kurt]

I want the user to be able to place entries, that allow references to cells that have formulas, but I want the formulas to be protected. The entries are made from a user form.

Hope this helps.

 

[lenze]

So why not just Lock the cells with formulas and Unlock the others and protect the sheet? You can find all cells with Formulas using Edit>Goto>Special>Formulas

lenze

 

[Kurt]

Thanks lenze I am trying it and it appears to work.

Do you mind looking up my previous post on list boxes and seeing if you can help on that one?

Thanks,

Kurt