Forum Discussion

Triki578's avatar
Triki578
Copper Contributor
Jun 18, 2020

Pop up boxes, based on dates in multiple columns

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?

excel
Macros and VBA

4 Replies

  • mathetes's avatar
    mathetes
    Gold Contributor
    Jun 18, 2020

    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.

    • Triki578's avatar
      Triki578
      Copper Contributor
      Jun 18, 2020

      Himathetes , 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

       

      • mathetes's avatar
        mathetes
        Gold Contributor
        Jun 18, 2020

        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.

Resources