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

jfarc

Active Member
Joined
Mar 30, 2007
Messages
308
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):
Code:
=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.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

BenMiller

Board RegularThe ONLY cool kid on the block
Joined
Nov 17, 2011
Messages
1,960
Office Version
  1. 365
Platform
  1. Windows
If you download the MoreFunc add-in, you can use =IF(ISERROR(--FORMULATEXT(A1)=A1),"","User Over-ride")
 

jfarc

Active Member
Joined
Mar 30, 2007
Messages
308
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
Why don't you try the built-in tracking tool(s?) that Excel has?
 

jfarc

Active Member
Joined
Mar 30, 2007
Messages
308

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
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.
 

jfarc

Active Member
Joined
Mar 30, 2007
Messages
308
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

New Member
Joined
Jul 10, 2012
Messages
1
Maybe you could use conditional formatting with this code snippet:
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,110
Messages
5,599,776
Members
414,337
Latest member
ogoodheart

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top