Pop up form depending on cell value

Question

I would like a pop up form named 'Question' to show up when a value in cell A9 in worksheet called 'Calculator' matches one of the values in Column O in Worksheet 'Data'.Values. In cell A9 are results of formula.

This code works but when I have other Excel workbooks open, it gives me 'Subscript out of range error'. I would like it to apply to this one particular workbook and not affect the other workbooks I have open.

Private Sub Worksheet_Calculate()

If IsError(Application.Match(Range("A9").Value, Sheets("Data").Columns("O"), 0)) Then Exit Sub

If Application.Match(Range("A9").Value, Sheets("Data").Columns("O"), 0) Then
    Question.Show
End If

End Sub

Answer 1

You could make the code reference only the workbook that the code is stored in:

Private Sub Worksheet_Calculate()
    With ThisWorkbook
        If IsError(Application.Match(.ActiveSheet.Range("A9").Value, .Sheets("Data").Columns("O"), 0)) Then Exit Sub
        If Application.Match(.ActiveSheet.Range("A9").Value, .Sheets("Data").Columns("O"), 0) Then
            Question.Show
        End If
    End With
End Sub

...It would be good to replace ActiveSheet with a specific sheet reference.