Pop up box Macro - Expired dates, and specific text

Butty578

New Member
Joined
Jun 28, 2020
Messages
5
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
Hi all,

New to doing Macros, and really not sure where to start, as what I've been trying doesn't seem to work very well.

I have a spreadsheet with a number of review dates, these are in columns K, S, V, X and AA, I am trying to make it so when these are equal to or past todays date, a pop up box appears telling you one is out of date (or multiple if more than one).

In the same spreadsheet, I am trying to get a pop up box to display if the date in column F, is a year old and the data in column G is equal to "specific text"

Both of these are in a worksheet call "Data"

I've managed to get a pop up to work when opening the spreadsheet (just testing/playing), but not on specific formats, any help greatly appreciated.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Butty578

New Member
Joined
Jun 28, 2020
Messages
5
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
Was meant to say, that I am trying to get the pop up box to appear when opening the spreadsheet.
 

Butty578

New Member
Joined
Jun 28, 2020
Messages
5
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
So for the first issue, I've got it working as the following;

VBA Code:
Sub Workbook_open()
   Dim r As Long
   Dim Msg As String
   With Sheets("Data")
       For r = 2 To 5000
           If .Range("K" & r).Value > 0 And .Range("AB1").Value > .Range("K" & r).Value Then
               Msg = Msg & vbLf & .Range("B" & r).Value & " requires at least one review"
           End If
       Next r
   End With
   MsgBox Msg
End Sub

I've tried to extend the "range" from just column "K" to columns "K, S, V, X and AA", but that's not working :( - Is this possible, or do I need to recreate the above code for each column?

I've had to enter "=Today()" into cell "AB1" - is there a way to make it so that it just automatically recognises todays date?

I've also managed to make it so the pop up shows a list rather than having to click each notification.

Thanks in advance, like I said, new to this.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,364
Messages
5,595,722
Members
414,013
Latest member
tnobbs

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
Top