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.

sbgib · Accepted Answer · 2021-01-04 08:43:01Z

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:

Select File > Options.

Click Trust Center, then Trust Center Settings...

Click Trusted Locations.

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.

Click OK.

answered Jan 4, 2021 at 8:43

sbgib

5,8583 gold badges22 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to show alert message in Excel VBA?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related