Private sub to generate Message Box

[howard]

I need a message box to be generated when opening a workbook and #value! appears in Cell E4 (have a hyperlink to a workbook in E4)

I have tried to write code to do this, but the message box does not appear

 Private Sub Workbook_Open()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Modify the sheet name as per your requirement
    
    If ws.Range("E4").Value = "#VALUE!" Then
        MsgBox "Double Click on Hyperlink to open Managers Comm File", vbInformation, "Open Managers Comm File"
    End If
End Sub

it would be appreciated if someone could kindly amend my code

 

[Jaafar Tribak]

How about this :

 Private Sub Workbook_Open()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Modify the sheet name as per your requirement
    If IsError(ws.Range("E4")) Then
        If Evaluate("=ERROR.TYPE(E4)") = 3& Then
            MsgBox "Double Click on Hyperlink to open Managers Comm File", vbInformation, "Open Managers Comm File"
        End If
    End If
End Sub

 

[howard]

Thanks for the help. I had the code inserted in the sheet module and not the workbook Module

 

[Jaafar Tribak]

Thanks for the help. I had the code inserted in the sheet module and not the workbook Module

Try fully qualifying the cell E4 as follows and see if it works:

If Evaluate("=ERROR.TYPE(Sheet1!E4)") = 3& Then

EDIT:
Just saw your last edit . Even if it now works for you, I would still recommend that you amend the code and fully-qualify the cell E4 as shown above in case Sheet1 is not the active worksheet when first opening the workbook

 

[howard]

Many thanks Jaafar

 

[Jaafar Tribak]

Many thanks Jaafar

You are welcome. Happy to help.