06-18-2020 07:25 AM
06-18-2020 07:25 AM
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?
06-18-2020 08:06 AM
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.
06-18-2020 08:59 AM - edited 06-18-2020 08:59 AM
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
06-18-2020 09:23 AM
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.
06-18-2020 12:03 PM