Force refresh/load Named Ranges on Workbook Open in Excel

mrwad

New Member
Joined
Oct 16, 2018
Messages
49
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):


0nvvG.png





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...-excel?noredirect=1#comment102675215_58144795
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top