Search code examples
excelerror-handlingexcel-addinsvba

How to Throw Error with Excel Prompt in VBA

I have some code which looks for a value with a given sheet name in two separate workbooks.

What I want to do is when the first workbook does not have the sheet, instead of the following prompt coming up, it cancels/throws an error and using the error handling goes to the second spreadsheet. How do I do this?

Sheet Select Prompt

Currently I am using this code to achieve this:

fFormString1 = "'" & wkBookRef1 & firstShtName & "'!$L$6/1000"
fFormString2 = "'" & wkBookRef2 & firstShtName & "'!$L$6/1000"
Application.DisplayAlerts = False 'Does nothing to the prompt
On Error GoTo tryTwo 'Following only throws error when prompt is canceled
    ThisWorkbook.Sheets("Place").Range("E53").Formula = "=" & fFormString1
    GoTo endTen

tryTwo:
    ThisWorkbook.Sheets("Place").Range("E53").Formula = "=IFERROR(" & fFormString2 & ","""")"
    On Error Resume Next
endTen:
Application.DisplayAlerts = True 'Does nothing to the prompt

Note: I wish to do this with the spreadsheet closed ideally. Or visually not present to improve speed and smoothness of operation for my client.

Solution

  • Borrowing heavily from Thomas' answer (full credit is due). However it seems that this didn't work for you.

    Use ExecuteExcel4Macro but ascribe the value to the variable val. Then check if this is the error you are looking for Error(2023).

    Please find the code below:

    'Check if the sheet exists in the workbook, used to check which forecast file one should look in
Function ExtSheetExists(formString) As Boolean 'Form string is a formula string with both the worksheet and the workbook
    Dim val As Variant
    'Tries to execute formula and throws error if it doesn't exist
    On Error Resume Next
    val = ExecuteExcel4Macro(formString)
    ExtSheetExists = (val <> Error(2023)) 'Returns False if the sheet does not exist based on Error 2023
    On Error GoTo 0
End Function