Protect formula on worksheet but allow changes to the worksheet

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,652
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?
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
What I think you mean is this:

Code:
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

Well-known Member
Joined
Jul 23, 2002
Messages
1,652
I will give it a try to I place this in the User Form Initialize event?
 

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
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

Well-known Member
Joined
Jul 23, 2002
Messages
1,652

ADVERTISEMENT

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

Legend
Joined
Feb 18, 2002
Messages
13,690
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

Well-known Member
Joined
Jul 23, 2002
Messages
1,652
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,630
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top