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

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" <(E-Mail Removed)> wrote in message
news:F6743F58-DEB2-4F03-92F2-(E-Mail Removed)...
>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

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

On Mar 18, 7:18 pm, Dave Peterson <peter...@verizonXSPAM.net> 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