How to show alert message in Excel VBA?

Question

I have an excel (saved as Excel Macro-Enabled Workbook) file that looks like this:

Now, I created this function that notifies the person whenever he opens the file.

Private Sub Workbook_Open()
    Dim i As Long, lastRow As Long

    lastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To lastRow
        If DateDiff("d", Cells(i, 4), Date) <= 90 Then
            MsgBox (Cells(i, 2) + " is expiring!")
        End If
    Next

End Sub

The procedure is set to Open and the object is set to Workbook, my problem now is it does not shows the alert message, unless I perform this: Open the Visual Basic developer tab -> Reopen the file -> and received this warning message: Warning: It is not possible to determine that this content came from a trustworthy source. You should leave this content disabled unless the content provides critical functionality and you trust its source and then clicked Enable Macros.

I am using excel-2007

Any help is much appreciated.

question from:https://stackoverflow.com/questions/65559677/how-to-show-alert-message-in-excel-vba

Answer 1

This is a security feature. The user must enable macros before any code can run. There's nothing you can do within your code to prevent this.

If the file is always in the same folder, you can add that folder as a trusted location so that the warning does not appear:

1. Select File > Options.
2. Click Trust Center, then Trust Center Settings...
3. Click Trusted Locations.
4. If [the folder containing the file] is not listed as a Trusted Location, click 'Add new location...'. Enter the path [...] or click Browse... to locate and select it.
5. Click OK.