Need to Pause VBA Script While Links Update

Question

The core problem appears to be in the sub procedure. For simplicity I placed this in the main procedure: The problem I am facing is in the following code:

Sub US_Sub01()

Dim NAtotal As Integer

NAtotal = Worksheets("Dashboard").Cells(20, "C").Value

MsgBox(“NAtotal”) 'This shows me how many remaining links need to be updated. Starts at 4540.

Do Until NAtotal = 0 'This loop should keep going until NAtotal hits zero.

NAtotal = Worksheets("Dashboard").Cells(20, "C").Value

DoEvents

Loop

NAtotal does not change from 4540 and never counts down. If I break the macro cell C20 immediately updates to zero.

Any suggestions?

Thanks, Steve

Answer 1

You will need to call the functions to calculate formulas and update links as follows:

Update formulas: Application.Calculate

Update Links: ThisWorkbook.UpdateLink

If you want a status update for this process you will need to loop through each cell in the workbook and use code like this for each cell:

ThisWorkbook.Worksheets("Sheet1").Range("A1").Calculate

Then set Worksheets("Dashboard").Cells(20, "C").Value = total cells - loop count but I think this will slow your code down too much to be useful but let me know if you want this and I can create the code.

If you want something to tell the user that the sheet is being updated you can do something like this:

Excel.Application.StatusBar = "Updating Links & Formulas"
'Changes the "Ready in the status bar to what you want it to say
Application.Calculate 'Calculate Formulas
ThisWorkbook.UpdateLink 'Update Links
Excel.Application.StatusBar = False 'Changes back to "Ready" status

Just adding a :Do Events onto the end of your Set ActiveWKB = Workbooks.Open("Sub01.xlsm") might work too.