suppress dialog boxes vba NOT applicatoin.displayalert = false

[martingaleh]

Here's my code:

aggregate = "'" & ThisWorkbook.Path & "\" & counter & "\[aggregator.xlsm]" & ws.Name & "'!percent"
            prior = "[" & counter & ".xlsm]" & ws.Name & "!" & counter & "MBO"
            If counter = qt Then
                rng.Formula = "=iferror(" & aggregate & ",""oddly the tab is missing"")"
            Else
                rng.Formula = "=iferror(" & prior & "," & aggregate & ")"
            End If

since it's designed to fail out, inevitably one of the links will be missing. Excel will then have this annoying dialog box that asks for a new link because the link is missing. I tried
application.displayalerts = false
and
application.enableevents = false

They both don't work. Is there another one?

 

[mikerickson]

INDIRECT will work with sheets on other workbooks, just not if the other workbook is closed.

It sounds like you have a Master workbook, that contains formulas that you want to refer to another workbook, when that workbook is opened.
The problem is that formulas have to refer to real worksheets in real workbooks, not hypothetical future sheets.

You could have the Master workbook use the Application level Workbook_Open event to detect when a new workbook is opened. Then, if that workbook is suitable, set all the formulas to have cells on that workbook be precedents.

Alternately, you could create a UserForm that would look to the avaiable (closed) workbooks and when you select one, open that workbook and write the formulas.

If you have lots of cells with formulas, but these many formulas refer to only a few ranges in the other book, changing Name definitions might be better than setting each cell's formula.