Pop up boxes, based on dates in multiple columns

%3CLINGO-SUB%20id%3D%22lingo-sub-1474306%22%20slang%3D%22en-US%22%3EPop%20up%20boxes%2C%20based%20on%20dates%20in%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1474306%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20never%20created%20a%20pop%20up%20box%20in%20excel%20before%20and%20I%20have%20no%20idea%20where%20to%20begin%20with%20the%20formula%2Fcoding.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20dates%20in%20the%20following%20columns%3B%20K%2C%20S%2C%20V%2C%20X%2C%20Z%3C%2FP%3E%3CP%3EI%20need%20these%20to%20create%20a%20pop%20up%20if%20the%20date%20is%2012%20Months%20in%20the%20past%20and%20column%20AB%20%3D%E2%80%9DActive%E2%80%9D%20as%20these%20dates%20will%20have%20expired%20and%20someone%20needs%20to%20do%20something.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20need%20a%20pop%20up%20if%20the%20dates%20in%20column%20F%20is%20a%20year%20in%20the%20past%20and%20column%20U%20shows%20%E2%80%9CSpecific%20Text%E2%80%9D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20will%20need%20to%20open%20the%20pop%20up%20each%20time%20the%20spreadsheet%20is%20opened.%3C%2FP%3E%3CP%3ETitle%20of%20worksheet%20this%20refers%20to%20is%20%E2%80%9CData%E2%80%9D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20a%20sidenote%2C%20I%20know%20with%20most%20formulas%2C%20if%20you%20add%2Fremove%20cells%20it%20Excel%20automatically%20changes%20the%20formulas%2C%20would%20the%20coding%20behind%20the%20pop%20up%20box%20do%20the%20same%2C%20or%20would%20I%20need%20to%20change%20that%20if%20columns%20get%20added%2Fremoved%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1474306%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1474428%22%20slang%3D%22en-US%22%3ERe%3A%20Pop%20up%20boxes%2C%20based%20on%20dates%20in%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1474428%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F701745%22%20target%3D%22_blank%22%3E%40Triki578%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20sure%20exactly%20what%20you%20mean%20by%20%22pop%20up%20box%22%20to%20begin%20with.%20There%20are%20drop-down%20boxes%2C%20that%20are%20used%20to%20provide%20valid%20answers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENevertheless%2C%20%3CEM%3Ewhat%20you're%20describing%20is%20usually%20accomplished%20by%20means%20of%20%3CSTRONG%3EConditional%20Formatting%3C%2FSTRONG%3E%3C%2FEM%3E%2C%20a%20capability%20you%20can%20find%20on%20the%20Home%20toolbar%2C%20or%20under%20the%20main%20menu's%20Format%20selection.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you'd%20post%20a%20sample%20of%20your%20file%2C%20somebody%20here%20can%20demonstrate%20it%20with%20your%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1474574%22%20slang%3D%22en-US%22%3ERe%3A%20Pop%20up%20boxes%2C%20based%20on%20dates%20in%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1474574%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%20%2C%20thank%20you%20for%20your%20reply%2C%20I%20don't%20think%20it%20is%20conditional%20formatting%2C%20I%20have%20colours%20that%20change%20on%20different%20conditions%2C%20this%20is%20a%20box%20that%20pops%20up%20when%20you%20open%20Excel%20to%20inform%20you%20dates%20have%20expired.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%20what%20I%20have%20found%20online%2C%20it%20would%20be%20something%20along%20the%20following%2C%20but%20I%20still%20haven't%20been%20able%20to%20get%20it%20to%20work%20properly%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EIf%20Not%20Intersect(Target%2C%20Range(%22Cells%22))%20Is%20Nothing%20Then%0AIf%20Target.Cells.Count%20%26gt%3B%201%20Or%20IsEmpty(Target)%20Then%20Exit%20Sub%0A%20%20%20%20If%20Target.Value%20%26lt%3B%20DateAdd(%22M%22%2C%20%2212%22%2C%20Date)%20Then%0A%20%20%20%20%20%20%20%20ans%20%3D%20%22Part%20Number%20%22%20%26amp%3B%20Target.Offset(%2C%20-2).Value%0A%20%20%20%20%20%20%20%20anss%20%3D%20%22Lot%20Number%20%22%20%26amp%3B%20Target.Offset(%2C%20-1).Value%0A%20%20%20%20%20%20%20%20MsgBox%20ans%20%26amp%3B%20vbNewLine%20%26amp%3B%20anss%20%26amp%3B%20vbNewLine%20%26amp%3B%20%22Is%20within%20the%208%20month%20requirement%22%0A%20%20%20%20End%20If%0AEnd%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1474650%22%20slang%3D%22en-US%22%3ERe%3A%20Pop%20up%20boxes%2C%20based%20on%20dates%20in%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1474650%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F701745%22%20target%3D%22_blank%22%3E%40Triki578%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWell%2C%20I%20avoid%20VBA%20and%20macros%20whenever%20possible%2C%20for%20the%20simple%20reason%20that%20I%20generally%20find%20there%20are%20easier%20ways%20already%20built-in.%20And%20conditional%20formatting--exactly%20as%20you%20have%20it%20already%2C%20it%20would%20appear--would%20be%20how%20I'd%20solve%20your%20problem.%20Maybe%20just%20different%2C%20more%20distinct%2C%20formats.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20any%20event%2C%20I%20can't%20help%20you%20(don't%20know%20the%20code)%20with%20the%20macro%20you%20found%2C%20but%20there%20are%20others%20here%20who%20can.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1475160%22%20slang%3D%22en-US%22%3ERe%3A%20Pop%20up%20boxes%2C%20based%20on%20dates%20in%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1475160%22%20slang%3D%22en-US%22%3EI%20agree%2C%20I%20generally%20avoid%20it%2C%20but%20it's%20been%20requested%20incase%20someone%20misses%20it%20(to%20others%20having%20it%20pop%20up%20is%20easier%20than%20checking%20the%20sheet).%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20anyway%3C%2FLINGO-BODY%3E
Highlighted
Occasional 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
Highlighted

@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.

Highlighted

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

 

Highlighted

@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.

Highlighted
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