Can I lock only the cells containing formulas?
 

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,
--
Jennifer Lee
IS Coordinator/App Support

Can I lock only the cells containing formulas?
 

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


Gary


"JennLee" wrote in message
...
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,
--
Jennifer Lee
IS Coordinator/App Support

Can I lock only the cells containing formulas?
 

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.

JennLee wrote:

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,
--
Jennifer Lee
IS Coordinator/App Support

--

Dave Peterson

Can I lock only the cells containing formulas?
 

On Mar 18, 7:18 pm, Dave Peterson wrote:
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.





JennLee wrote:

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,
--
Jennifer Lee
IS Coordinator/App Support

--

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