Force refresh/load Named Ranges on Workbook Open in Excel

[mrwad]

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)


        'ENSURE NAMED RANGES ARE LOADED (CODE HERE)
                            
            DoEvents
                            
                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
                Else
                    ThisWorkbook.Sheets("MAIN").Shapes("LimitRequest").Visible = False
                    ThisWorkbook.Sheets("MAIN").Shapes("CreditCheck").Visible = True
                End If
        
        oFractionComplete (0.2)
        ........

https://stackoverflow.com/questions...-excel?noredirect=1#comment102675215_58144795