Pop up boxes, based on dates in multiple columns

Copper Contributor

I've never created a pop up box in excel before and I have no idea where to begin with the formula/coding.

 

I have dates in the following columns; K, S, V, X, Z

I need these to create a pop up if the date is 12 Months in the past and column AB =”Active” as these dates will have expired and someone needs to do something.

 

I also need a pop up if the dates in column F is a year in the past and column U shows “Specific Text”

 

This will need to open the pop up each time the spreadsheet is opened.

Title of worksheet this refers to is “Data”

 

As a sidenote, I know with most formulas, if you add/remove cells it Excel automatically changes the formulas, would the coding behind the pop up box do the same, or would I need to change that if columns get added/removed?

4 Replies

@Triki578 

 

Not sure exactly what you mean by "pop up box" to begin with. There are drop-down boxes, that are used to provide valid answers.

 

Nevertheless, what you're describing is usually accomplished by means of Conditional Formatting, a capability you can find on the Home toolbar, or under the main menu's Format selection.

 

If you'd post a sample of your file, somebody here can demonstrate it with your data.

Hi@mathetes , thank you for your reply, I don't think it is conditional formatting, I have colours that change on different conditions, this is a box that pops up when you open Excel to inform you dates have expired.

 

From what I have found online, it would be something along the following, but I still haven't been able to get it to work properly;

 

If Not Intersect(Target, Range("Cells")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Target.Value < DateAdd("M", "12", Date) Then
        ans = "Part Number " & Target.Offset(, -2).Value
        anss = "Lot Number " & Target.Offset(, -1).Value
        MsgBox ans & vbNewLine & anss & vbNewLine & "Is within the 8 month requirement"
    End If
End If
End Sub

 

@Triki578 

 

Well, I avoid VBA and macros whenever possible, for the simple reason that I generally find there are easier ways already built-in. And conditional formatting--exactly as you have it already, it would appear--would be how I'd solve your problem. Maybe just different, more distinct, formats.

 

In any event, I can't help you (don't know the code) with the macro you found, but there are others here who can.

I agree, I generally avoid it, but it's been requested incase someone misses it (to others having it pop up is easier than checking the sheet).

Thank you anyway