Warning message with VBA when opening file

[Beneindias]

Hi guys.

I'm a noob working with VBA, so I need your help.

So, I have an excel file, where I have a table with some data.
In this table, I have a collumn named "expiration date" (column G), but not all rows have expiration dates, some of them are empty.

I have setup a conditional formating, that compares expiration date with current date and changes colors of the entire row.

But, I need something better:
I need to have a Warning message to pop when I open the file, to tell me that I have some product that as passed the expiration date.
This VBA code needs to ignore empty cells and cells with text in that collumn.
This is in a table, so it would be awesome if it would work when rows are added to the table.
I only want the warning if I have some product that has passed the date.

Tried the next script, but it gave me an error "Compile error: Sub or Function not defined.":

Private Sub Workbook_Open()
If Worksheets("Folha1").Range("$G9").Value < TODAY() Then
MsgBox "Tem declarações expiradas", vbOKOnly
End If
End Sub


Is my script bad, or am I doing something wrong?
Apreciate all your help.

 

[My Aswer Is This]

Try this:

If Worksheets("Folha1").Range("G9").Value < TODAY() Then

See I removed the $

 

[Beneindias]

Try this:

If Worksheets("Folha1").Range("G9").Value < TODAY() Then

See I removed the $

Thanks for your reply.

Meanwhile, I changed my script to the following:

Private Sub Workbook_Open()
If Worksheets("Folha1").Range("G9").Value < Date Then
MsgBox "Tem declarações expiradas", vbOKOnly
End If
End Sub

Now, it works, but only on that specific cell (G9), but I need it to work on the entire column (G).

 

[My Aswer Is This]

So are you saying you have a range of dates in column G
From like Range("G1:G45")
And if any of these dates are less then Today you want a message to popup.
And the message will always be the same.
If this what you want?

 

[Beneindias]

So are you saying you have a range of dates in column G
From like Range("G1:G45")
And if any of these dates are less then Today you want a message to popup.
And the message will always be the same.
If this what you want?

Yes, that´s it.

But it should allow me to add or delete rows to the table and still work properly.

 

[My Aswer Is This]

You need to put this script in your workbook:

Sub Check_For_Dates()
'Modified 6/21/2022  12:20:15 PM  EDT
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "G").End(xlUp).Row
    For i = 1 To Lastrow
        If Cells(i, "G").Value < Date Then MsgBox "Tem declarações expiradas", vbOK
    Next
End Sub

And this script which will run when you open the workbook
Private Sub Workbook_Open()
Call Check_For_Dates
End Sub

 

[My Aswer Is This]

I need to modify the script to include sheet name:
Use this script:
Other script stays the same

Sub Check_For_Dates()
'Modified  6/21/2022  12:49:31 PM  EDT
Application.ScreenUpdating = False
Dim Lastrow As Long

With Worksheets("Folha1")
    Lastrow = .Cells(Rows.Count, "G").End(xlUp).Row
    For i = 1 To Lastrow
        If .Cells(i, "G").Value < Date Then MsgBox "Tem declarações expiradas", vbOK
    Next
End With
End Sub

 

[Beneindias]

I need to modify the script to include sheet name:
Use this script:
Other script stays the same

Sub Check_For_Dates()
'Modified  6/21/2022  12:49:31 PM  EDT
Application.ScreenUpdating = False
Dim Lastrow As Long

With Worksheets("Folha1")
    Lastrow = .Cells(Rows.Count, "G").End(xlUp).Row
    For i = 1 To Lastrow
        If .Cells(i, "G").Value < Date Then MsgBox "Tem declarações expiradas", vbOK
    Next
End With
End Sub

It worked!

Thank you, I would never get to that solution alone.

 

[My Aswer Is This]

It worked!

Thank you, I would never get to that solution alone.

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.