Can I lock only the cells containing formulas?

J

JennLee

I desperately need to be able to lock cells containing formulas in the
worksheets to which the data is being captured via my custom UserForm (that's
housed on Sheet 1). I have to present my spreadsheet application to several
directors tomorrow and I really need to have this functionality in place! I
have several staff members that sometimes click into a cell after they've
entered their data and ACCIDENTLY erase the formula.
I've tried password protecting the sheets but them I get an error when
entering the data via the UserForm (which is coded to send data to lastrow
UNDER all my cells that contain the formulas. HELP PLEASE

Thanks in advance for any guidance,
 
G

Gary Keramidas

maybe something like this:

sub lock_formulas
Dim cell As Range
Application.ScreenUpdating = False
ActiveSheet.Unprotect
For Each cell In Cells.SpecialCells(xlFormulas, 23)
cell.Locked = True
Next cell
end sub
 
D

Dave Peterson

Select your range to lock (include all the cells with the formulas -- and even
constants, too).

Edit|goto|Special
Select Formulas
and your range is now smaller
Format|cells|protection tab

and lock those cells with the formulas.

Make sure you unlock the cells that can be changed.

And then protect the worksheet.
 
G

George

Select your range to lock (include all the cells with the formulas -- and even
constants, too).

Edit|goto|Special
Select Formulas
and your range is now smaller
Format|cells|protection tab

and lock those cells with the formulas.

Make sure you unlock the cells that can be changed.

And then protect the worksheet.







--

Dave Peterson- Hide quoted text -

- Show quoted text -

Easy:

Select the whole worksheet, then go to Format\Cells\Protection,
uncheck "Locked" box and click "Ok" button.

Follow what Dave wrote to lock cells with formulas and protect your
worksheet.

Done.

Thanks,

George
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top