How to programatically determine if a particular Cell contains user entered Value or Formula?

[jfarc]

I have a worksheet with formulas installed, but wish to allow the user to over-ride the formula and hand-key a value in the particular cell.

But, how can I know (either by VBA or another formula in an adjacent cell) if the user has chosen to over-ride the formula and key in a value in that cell?

As for an example of what I mean, albeit very simplified:

Formula in Cell (A2):

=rHours*.2

If rHours equals 10, then the resulting value of Cell (A2) would be '2'. I want the user to be able to over-ride this formula and enter what they want in cell (A2), but I want Cell (B2) to contain the value "User Over-ride" if the user truly enters something on their own. They may even 'play around' with entering various values and end up hand entering a '2' in cell (A2). In this scenario, I still want the cell (B2) to have the "User Over-ride" value if this is the case.

 

[BenMiller]

If you download the MoreFunc add-in, you can use =IF(ISERROR(--FORMULATEXT(A1)=A1),"","User Over-ride")

 

[jfarc]

Thanks Ben, but this workbook goes out to dozens of different people/different companies and requiring all to load Add-ins wouldn't be feasible.

Any other solution with the standard functions?

 

[Norie]

Why don't you try the built-in tracking tool(s?) that Excel has?

 

[jfarc]

Well, never knew about the 'Tracking Tool'. Looks pretty good. I'll have to use it a bit to see if it will suffice.

The one thing I can't see it doing is since it's cursor highlight activated (or visually seeing the corner tab of the cell as an indicator), this will only work if the user is viewing the particular worksheet. In my scenario, the current worksheet is a detail page (multiple of these) that all feed to a Summary page. The Summary page is where I need to highlite that the formulas on the detail page have been over-ridden. Then the user can go to the detail page if desired.

 

[Norie]

The only way I could see you keeping track of every change would be to us a worksheet change event.

It wouldn't be hard to write code for that, depends on what sort of things you would want to log I suppose.

For example you would obviously want to log the new value/formula, the user that made the change, their favorite colour, the date/time the change was made etc but there might be other things.

One thing though is that you might end up with a lot of data if the workbook(s) you are monitoring are used by a lot of people.

 

[mikerickson]

Condtional formatting the summary sheet via named functions that refer to detail sheets and use GET.CELL might do what you want.

About GET.CELL
http://www.mrexcel.com/forum/showthread.php?20611-Info-only-get-cell-arguments

 

[jfarc]

Thanks Norie. I've been pulled into other projects and had to shelf this one, but back on it.

Mike, I have used the Get.Cell in other areas in the past, but I guess for similar functionality. Thanks for reminding me about it.

In the meantime, I ended up Locking/Hiding the original formula cell and allowed the user to enter over-ride values in an adjacent cell. Then a 3rd cell would determine if the original formula was being used or an over-ridden entry. Seems to work ok, albeit a work-around. I'd like to see if a WS Change Event / Get.Cell would do what I want.

 

[Geeko]

Maybe you could use conditional formatting with this code snippet:

Function ContainsFormula(Cell As Range)Dim strSearch As String


strSearch = Cell.Formula
strSearch = Left(strSearch, 1)
If strSearch = "=" Then
    ContainsFormula = False
Else
    ContainsFormula = True
End If


End Function

I use this to color each cell in which a user entered own values.

Thanks Norie. I've been pulled into other projects and had to shelf this one, but back on it.

Mike, I have used the Get.Cell in other areas in the past, but I guess for similar functionality. Thanks for reminding me about it.

In the meantime, I ended up Locking/Hiding the original formula cell and allowed the user to enter over-ride values in an adjacent cell. Then a 3rd cell would determine if the original formula was being used or an over-ridden entry. Seems to work ok, albeit a work-around. I'd like to see if a WS Change Event / Get.Cell would do what I want.

 

[jfarc]

Thanks Geeko, I'll give that a try.

And welcome to the forum!