+ Reply to Thread
Results 1 to 3 of 3

Referenced cells #REF during VBA execution but then display after????

  1. #1
    Registered User
    Join Date
    01-15-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    18

    Referenced cells #REF during VBA execution but then display after????

    Good Afternoon All,

    I am having a weird problem that only happens sometimes. I have a master workbook that uses cell references to essentially project a ReadOnly mirror image of a daily worksheet housed in a different workbook on the network. The daily workbooks are titled by their respective date, 4-1-18, 4-2-18, etc. I made a few buttons to enable the user to navigate, +- 1 day for instance. So, you click a button and it programmatically updates the cell to reference the respective day by changing the file path in the cell formula.

    Everything works fine and dandy but occasionally it'll update the references and for about a half second or so every cell shows #REF but then flips to the correct value. Normally this wouldn't be an issue but I need the actual values during execution so that I can assign formats to the ReadOnly Mirror sheet. It seems to be a connectivity issue or something since the #REF doesn't always show and it always resolves itself after a half second or so. Is there a way I can run a Do Until Excel fixes itself Loop or something that gives it times to refresh the data?

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Referenced cells #REF during VBA execution but then display after????

    I don't understand this part:
    Quote Originally Posted by jpullen88 View Post
    I need the actual values during execution so that I can assign formats to the ReadOnly Mirror sheet.
    Could you explain a little more as to why this is an issue?

    Perhaps using a Wait command to add a pause to the current code would help? Just to let Excel catch up before continuing?

    Perhaps switching Application.Screenupdating to False would help?

    Difficult to say without seeing the workbooks and the routine in action I'm afraid.

    BSB

  3. #3
    Registered User
    Join Date
    01-15-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    18

    Re: Referenced cells #REF during VBA execution but then display after????

    Ok here is the main sub. The code works fine unless the cells get stuck in #REF, hence why it works 9/10 times. The reason that is an issue is because I need the values to identify the last row. I need the last row to run some formatting code. I tried using wait and it just seemed to wait - not wait and let the data refresh. I have an optimization sub I run early on that has screenupdating = false. I tried setting it to true but that didn't help either. I can't really post the workbook as all the links are on a server and it is sensitive data. Let me know what you think.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Restrict VBA execution to a range o cells!
    By Hellion in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-24-2016, 12:59 PM
  2. [SOLVED] How to get sheet reference to display nothing if cell referenced is blank
    By jmrlifesafe in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-08-2016, 04:36 PM
  3. [SOLVED] Macro hides rows on button press but other Functions referenced then display ERROR message
    By dougielb in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-29-2015, 09:32 AM
  4. Eliminate display alerts during macro execution
    By duugg in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-08-2014, 04:40 PM
  5. Display referenced cell and related hyperlinks
    By pepe.r.taylor in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-05-2011, 08:10 AM
  6. Sorting Referenced Cells in VBA and Keeping Them Referenced
    By BrownTeddyBear in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-08-2009, 06:15 AM
  7. [SOLVED] Is there a way to toggle cells between display of referenced data.
    By williejoeshaver in forum Excel General
    Replies: 3
    Last Post: 03-11-2005, 08:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1