Warning message with VBA when opening file

Beneindias

Board Regular
Joined
Jun 21, 2022
Messages
120
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Try this:
If Worksheets("Folha1").Range("G9").Value < TODAY() Then

See I removed the $
 
Upvote 0
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).
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
You need to put this script in your workbook:
VBA Code:
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
 
Upvote 0
I need to modify the script to include sheet name:
Use this script:
Other script stays the same

VBA Code:
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
 
Upvote 0
Solution
I need to modify the script to include sheet name:
Use this script:
Other script stays the same

VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top