Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Star Lounger
    Join Date
    Nov 2008
    Location
    England, Yate, Bristol
    Posts
    51
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Cell reference keeps reverting to data result

    Hi
    I have a cell F13 and I enter this formula into the cell: =O9

    Cell O9 contains a formula to return a number formatted as General, say (15) without the brackets.

    Cell F13 displays the result (15), but the formula has gone and changing O9 has no further effect on F13.

    I can't get the formula to remain in F13.

    Please help!

    Peter

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    if you enter =O9 in cell F13 and it turns into a value, removing the formula altogether (as you are suggesting), then it suggest to me that you have a macro running (probably a change macro). Look in the worksheet code or this workbook code for event macros.

    If the formula remains, but the value does not update when the value in O9 changes, it suggests to me that the workbook may have manual calcuation on. Does Pressing <F9> update the value? If so, File - options - formulas and then select "Automatic" near the top (under "Calculation Options") should solve the problem.

    Steve

  3. #3
    Star Lounger
    Join Date
    Nov 2008
    Location
    England, Yate, Bristol
    Posts
    51
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Well Steve I suspect you are right.
    I built the worksheet for data entry with the calculated result all working OK.
    I then had a brainwave and built a VBA userform to step through the data entry fields and write from the userform text fields to the spreadsheet cells and I did use some on_click events.

    I'll have to go through and see what's happening although, would an on_click event for a textbox on a userform write to a cell if the userform were not running?

    Peter

    Thanks for the help
    Last edited by peterinth; 2013-03-18 at 11:15. Reason: forgot thanks

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I'll have to go through and see what's happening although, would an on_click event for a textbox on a userform write to a cell if the userform were not running?
    No it shouldn't. I suspect a Worksheet_Change (the sheet object) or a Workbook_SheetChange (in the thisWorkbook object) running to have to change the formula to a value as you describe.

    Steve

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    peterinth (2013-03-19)

  6. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    ..or perhaps an OnEntry event like..
    ThisWorkbook.Worksheets("Sheet1").OnEntry = "someRoutine"

    zeddy

  7. The Following User Says Thank You to zeddy For This Useful Post:

    peterinth (2013-03-19)

  8. #6
    Star Lounger
    Join Date
    Nov 2008
    Location
    England, Yate, Bristol
    Posts
    51
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks Steve and zeddy

    Every time I change the list box the formula is lost. I have searched the VBA code for ThisWorksheet and the userform, but cannot find any reference to the cell that's changing.
    It is linked to the cell F13 changing as I proved by elimination and have worked around it by not using cell F13 at all.
    I am very frustrated because it is obviously event linked, but I cannot find the code causing it.

  9. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    On the sheet that is giving the problem, right-click on the sheet tabname, then select 'View Code'.
    This will take to event routines for that particular sheet.
    I think you'll find it there.
    It probably won't be in a general Module.

    zeddy

  10. #8
    Star Lounger
    Join Date
    Nov 2008
    Location
    England, Yate, Bristol
    Posts
    51
    Thanks
    4
    Thanked 0 Times in 0 Posts
    No, no code there.
    Here is a screen shot?

    HRerror.jpg

  11. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Peter

    OK. Then it must be in a module. But you might not find any specific reference to cell F13.
    It could be because F13 is within a particular range that is being monitored.

    Of course, we could find out why for you, but you would need to post a copy of the file.
    Is it possible to do that by making a copy of the file, and putting in dummy names and values so no sensitive data is included?

    zeddy

  12. #10
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by peterinth View Post
    No, no code there.
    Here is a screen shot?

    HRerror.jpg
    Would it be possible for you to upload a copy of the sheet so that we can have a play with it?

  13. #11
    Star Lounger
    Join Date
    Nov 2008
    Location
    England, Yate, Bristol
    Posts
    51
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Love to have your help thanks.

    I'll change the details to protect the innocent and, if I can work out how, post a copy.

  14. #12
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    If you set the troublesome cell to Locked and Protect the Worksheet from changes, you may discover which bit of VBA is trying to change the cell when the debugging screen comes up . . .

  15. #13
    Star Lounger
    Join Date
    Nov 2008
    Location
    England, Yate, Bristol
    Posts
    51
    Thanks
    4
    Thanked 0 Times in 0 Posts

    With attached file

    Please excuse the messy coding.

  16. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Peter

    Thanks for the file.
    I can replicate your reported problem.
    I'm not surprised you are having difficulty with resolving this.
    I'm working on it.
    Will report back.

    zeddy

  17. #15
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Yep - I can replicate the problem too! Most mysterious.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •