Protected Formulas

**georgeeasten** · 02-08-2008, 10:38 PM

Hi,

Just wondering if it is possible to have a formula in a cell but over write it with other digits while preserving the underlying formula?

I can lock the sheet/workbook but cant edit the cells once this is done?

I'm hoping to be able to delete what ever I have overwritten and for the formula to then take effect again.

Thanking you in advance

**mikerickson** · 02-08-2008, 11:01 PM

This UDF routine should do the job.

Put this in a normal code module.

Please Login or Register  to view this content.

And put this in the ThisWorkbook code module

Please Login or Register  to view this content.

If a spreadsheet formula involves the new function CellEntry(), entering text into that cell will not overwrite the formula. It will set the value returned by CellEntry(). For example:

Enter the formula =CellEntry() in a cell.
The cell will appear empty and the formula "=CellEntry()" will be in the formula bar
Enter "cat" into the cell
The cell now shows "cat", the value of the formula "=CellEntry()", which is shown in the formula bar.

To use it in your sitution. If you want a cell to have the formula =A1+1 unless it has been overwritten, put this formula in a cell

=IF(CellEntry()<>"",CellEntry(),A1+1)

If what has been typed in the cell is "", the formula returns A1+1.
When something has been typed into the cell, the formula returns what was typed.

NOTES:
If a cell has a formula that includes the CellEntry UDF:
a) entering a constant into the cell sets the value of CellEntry to that constant as text.

b) entering a formula into that cell changes the formula in the cell, but not its CellEntry value

c) clearing the contents of that cell sets the value of CellEntry() to vbNullString. It does not remove the formula from the cell.

To remove a CellEntry formula from a cell, either delete the cell or enter a non-CellEntry formula (eg "=3") and then delete that formula.

Copy/Pasteing a CellEntry formula cell also copies the CellEntry value.

**georgeeasten** · 02-08-2008, 11:12 PM

Well thanks for the answer but I think that is way above my head.

I think I kind of understand the scripting (it looks like VB) and hopefully when I come back from my course I will be able to understand it more. At the moment I don't even really fully understand how to copy into the 'Code Modules'

In the mean time, stupid question I know but... is there an easier way of doing it???

Thanks

**mikerickson** · 02-09-2008, 12:39 AM

Not realy.
Entering text into a cell overwrites any existing formula.

There are ways ease the restoration of formulas that have been overwritten (Defining a Name as a function can save typing), but VB is needed for it to be automatic.

**georgeeasten** · 02-09-2008, 12:27 PM

How do I add that script so it will work with excel??

I might as well give it a go, I've got to learn sometime!

**VBA Noob** · 02-09-2008, 12:38 PM

First block of code in a normal module (See link)

http://www.contextures.com/xlvba01.html#Regular

Second block goes in ThisWorkbook
http://www.contextures.com/xlvba01.html#Workbook

Link has some basic in macro. More useful links below

http://www.bettersolutions.com/excel...R646543531.htm

VBA Noob

**georgeeasten** · 02-09-2008, 02:44 PM

I LOVE VB!!!

Thank you guys you're geniuses! I am now sitting down for a very long few weeks and teaching myself VB.

**georgeeasten** · 02-11-2008, 05:52 PM

Hello again!

I have been using the script given above but unfortunately when you over write the formula with another number formulas that refer to that cell do not calculate with the new number.

is it going to be possible to have any given formula read the number showing?

**mikerickson** · 02-12-2008, 01:51 AM

My testing had formulas dependent on the CellEntry() cell react to the number showing.

One possibility you might think about; CellEntry() returns a string, that will cause problems if the dependent formula uses comparisons (< + >). Using the VALUE function will fix some cases.
(eg. =IF(VALUE(A1)<2,"x","y") will treat "1" and 1 in A1 the same way.)

If you could attach your workbook (redacting any confidential data) and an example of what it does wrong and what you want it to do, that would help in sussing this out.

**mikerickson** · 02-12-2008, 05:47 AM

This version of the UDF (in the normal module) will allow the user to overwrite with either numbers or text.
(The previous version was text only.)

Please Login or Register  to view this content.

(Both this and the previous version need the same companion routine to be in ThisWorkbook.)

**georgeeasten** · 02-12-2008, 01:39 PM

Works a treat, thank you again.

I think by bothering you is done now!

**mikerickson** · 02-12-2008, 04:14 PM

You're welcome.

I don't consider giving me an interesting puzzle to be "bothering". I had fun with this.

If you have other questions, the forums will be here.

**Charlie7** · 12-09-2018, 11:26 PM

Hi,

I'm trying to use the same code as you posted earlier. I think "IF(CELLENTRY..." has issues restoring an IF formula within itself?? Maybe i'm typing the function wrong. Could you please help? My formula looks like this:

=IF(CellEntry()<>"",CellEntry(),IF(D19="Weekly",RATE(C16,-J15,(C17+C18))*52,IF(D19="Fortnightly",RATE(C16/2,-J15,(C17+C18))*26,IF(D19="Monthly",RATE(C16*12/52,-J15,C17+C18)*12))))

I want the cell to restore an IF function, if the client doesn't manually put in an input.

Thanks!

**FDibbins** · 12-10-2018, 12:47 AM

Charlie, welcome to the forum

Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.