Force refresh/load Named Ranges on Workbook Open in Excel


New Member
I have a lot of Named Ranges that are coming from our File Management system metadata. Usually on workbook open it takes some time (up to 5 seconds) to load these values to cells. I have some other processes linked to these cells. For example showing/hiding shapes if let's say cell A1 > 1. The problem is that now there is no code to identify are these values already loaded so that another code can be performed.

Question: Is there any way to force load Named Ranges values by VBA? Or ensure they are loaded?

This is how Name Manager looks like (all these ="" are filled with values on Workbook_Open):

Here is my current code:

    Private Sub Workbook_Open()
    On Error Resume Next
    'Application.Visible = False
        Loading.LabelProgresso.Width = 0
        Loading.Show (vbModeless)
        oFractionComplete (0)
            ThisWorkbook.Worksheets("MAIN").ScrollArea = "$A$1:$BL$45"
        oFractionComplete (0.1)

                If ThisWorkbook.Sheets("Price calculation").Range("G1866") > 500000 And _
                    ThisWorkbook.Sheets("Other Data").Range("U7") = "value" Or _
                    ThisWorkbook.Sheets("Other Data").Range("T31") > 500000 Then
                    ThisWorkbook.Sheets("MAIN").Shapes("LimitRequest").Visible = True
                    ThisWorkbook.Sheets("MAIN").Shapes("CreditCheck").Visible = False
                    ThisWorkbook.Sheets("MAIN").Shapes("LimitRequest").Visible = False
                    ThisWorkbook.Sheets("MAIN").Shapes("CreditCheck").Visible = True
                End If
        oFractionComplete (0.2)

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...