Force refresh/load Named Ranges on Workbook Open in Excel

mrwad

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:


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/58144795/force-refresh-load-named-ranges-on-workbook-open-in-excel?noredirect=1#comment102675215_58144795
 

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...
Top