Display different message box based on cell value

[DanGK1]

Hello

I have a workbook that can open a URL into IE when a project number is entered in an input box.

What I would like to happen is that if the project number entered does not have a URL against it and the value is #N/A, then the message box will display "Project does not exist". However if the number does exist, I want a different message box to be displayed which is a yes/no box.

The code below is what I have tried which works fine if the cell value is #N/A but when it is not it throws an Run-Time error 13: Type mismatch

Sub SearchPidsMessageBox()
Dim Answer As String
Dim MyNote As String
If CVErr(Range("o2").Value) = CVErr(xlErrNA) Then
  MsgBox "PID PR" & Range("n2").Value & " Does not exist", , "Project ID not found"
ElseIf CVErr(Range("o2").Value) <> CVErr(xlErrNA) Then
'Else
'I have tried ElseIF and just Else and both give same error
    'Place your text here
    MyNote = "PID PR" & Range("n2").Value & " not allocated. Open URL?"
    'Display MessageBox
    Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Project ID Not Found")
    If Answer = vbNo Then
        'Code for No button Press
    Else
        'Code for Yes button Press
        Call LoadWebPage
    End If
End If
End Sub

Any help greatly appreciated.

Thanks

Dan

 

[BiocideJ]

The following code appears to work as you request.

The issue was that the CVErr() function takes an error number as its argument and when Range("O2").Value isn't an error it returns something else (I assume text) which is a type mismatch (string instead of Number) to what the function is expecting.

Sub SearchPidsMessageBox()
Dim Answer As String
Dim MyNote As String
    If IsError(Range("o2").Value) Then
      MsgBox "PID PR" & Range("n2").Value & " Does not exist", , "Project ID not found"
    Else
    'I have tried ElseIF and just Else and both give same error
        'Place your text here
        MyNote = "PID PR" & Range("n2").Value & " not allocated. Open URL?"
        'Display MessageBox
        Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Project ID Not Found")
        If Answer = vbNo Then
            'Code for No button Press
        Else
            'Code for Yes button Press
            Call LoadWebPage
        End If
    End If
End Sub

 

[DanGK1]

that's done it. Thanks for your help