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?

 

[Coding4Fun]

It looks like there is some code missing there, not sure what or where the issue is based on what has been posted.

However have you tried using error handling to capture this and resolve it vs trying to suppress it?

Sub DoSomething()
On Error GoTo MyErrorCode:

' Your code here

Exit Sub

MyErrorCode:
Msgbox(Err.Number & " - " & Err.Description)

If Err.Number = "Whatever your Err.Number returns as" Then
Resume Next
' Exit Sub
' Something = "Default Link"

End Sub

 

[martingaleh]

I probably wasn't being sufficiently clear because I thought it was a common problem. It's not an error. Here's a reduced case. Open a new workbook, make and run this:

Sub hi()
ActiveSheet.Cells(1, 1).Formula = "=iferror([fail.xlsx]sheet1!$a$1,""sheet doesn't exist"")"
End Sub

And prevent the dialogue that appears from the attached picture from appearing. I have thousands of these cells dynamically generated by vba and because spreadsheets come in and out of the directory, i can't just test for the files existence at run time.

https://pasteboard.co/HnSAW2U.png

Oh, you can't attach pictures anymore on this board?

 

[martingaleh]

I want to add some more failures to the collection.
https://stackoverflow.com/questions...-box-from-opening-every-time-a-cell-with-a-li

ThisWorkbook.UpdateLinks = xlUpdateLinksNever

or

ThisWorkbook.updateremotereferences = false

also doesn't work

 

[Coding4Fun]

Here's a reduced case. Open a new workbook, make and run this:

Sub hi()
ActiveSheet.Cells(1, 1).Formula = "=iferror([fail.xlsx]sheet1!$a$1,""sheet doesn't exist"")"
End Sub

Here is an example based on the reduced case you provided.

Sub hi()
Dim myWB As Workbook
On Error GoTo myError:
Set myWB = Workbooks("fail.xlsx") ' It will error out if fail.xlsx is not found.


ActiveSheet.Cells(1, 1).Formula = "=iferror([fail.xlsx]sheet1!$a$1,""sheet doesn't exist"")"


Set myWB = Nothing


' End if you dont hit an error
Exit Sub


' If you hit an error go here
myError:
ActiveSheet.Cells(1, 1).Value = "sheet doesn't exist"


Set myWB = Nothing


End Sub

 

[martingaleh]

I think the problem with your approach is if it does suppress the dialogue, it will set the active sheet to "sheet doesn't exist". The purpose of the iferror(xxxx) syntax is to allow for the fact that the sheet won't exist when the formula is first set upon the sheet, but later, when the sheet in a new workbook does exist, the formula will detect it and will be set to the value extracted from that formula, and not set to "sheet doesnt' exist"

So again, I actually do want the equation on the cell to be iferror(xxx) on the sheet regardless of whether the sheet exists or not. I don't want the dialogue to ask me to update the link afterwards.

 

[mikerickson]

the sheet won't exist when the formula is first set upon the sheet, but later, when the sheet in a new workbook does exist, the formula will detect it and will be set to the value extracted from that formula

Excel doesn't work that way for me.
As a test, I opened a new workbook and entered =Sheet2!$A$1 in a cell. Since there was no Sheet2, I got that "link" dialog you mention before.
I canceled out of that. The cell with the formula then showed #REF .
I then added a new sheet, Sheet2.
The previous formula did not detect the new sheet and it remained a #REF error.
I would recommend adding the sheets before adding the formula.

 

[martingaleh]

Wow, I can't believe it. Even though the formula is valid when you enter the sheet, when excel recalculates, it doesn't update the internal reference to the sheet to reflect the reference delineated by the formula. You have to hit f2-enter to have it update the reference internally. This is probably to speed up the calculation because you don't have to constantly parse the formulas, only when they're changed. I wonder if there's a way to force excel to update the reference? This is a bug.

I can't add the sheet before adding the formula because the issue isn'twhether a sheet exists or not. It's whether I can coalesce or reduce a series of values based on which one exists. By doing that, I can use the same formula everywhere. Otherwise, i have to use vba to update each formula everytime I run the workbook. This will result in more technical sophistication by the end user.

 

[mikerickson]

There is a workaround for the formula in post #7

=INDIRECT("Sheet2!$A$1")

which will react when Sheet2 is added.
But INDIRECT slows a worksheet down hugely.

A better approach might be to use the Workbook_NewSheet event to detect when a sheet is added and then add appropriate formulas where needed.

 

[martingaleh]

I originally avoided indirect because it doesn't work with sheets on different workbooks for some reason I forgot. Also, I'm trying to detect the existence of another workbook, not the addition of a new sheet on the workbook holding the formula. My worbook is like a controller workbook that accepts any number of other workbooks from department heads and then pulls the data based on which workbook is present. It's easiest to do something like iferror([workbooka]sheet1!a1,[workbookb]sheet1!a1), but if excel can't update that then we have a problem.

Maybe I'll try indirect again.