You are here:

Excel/Pop up in Excel 2013


QUESTION: I have created a huge spreadsheet in Excel 2013 which includes all field personnel information and certifications.  It currently holds 626 rows and will grow as new employees are added.  I'm looking to create pop up windows for each certification that is expiring 2 weeks before it actually expires and reference back to the employee in column C.  Example column AB "DL expiration"; column AD "DOT expiration"; Column AG "MA Hoist expiration"; Column AK "RI Hoist expiration" and so on and so forth.  How do I attempt to do this.  Is it one complete macro or several macros for each column.  Your help would be greatly appreciated.

ANSWER: Heather,

Personally I would use conditional formatting to highlight expiring certification.  You could quickly scroll through the sheet and see what is marked.  Additionally, you could put a column on the right side of your data.  For each row it would check each of the dates and see if they are withing two weeks of today or later.  If any are it would produce an X and if not it would produce a null string "".  You could then filter the data so just the X rows show and the conditional formatting would quickly show which certification is expiring in each row.

Pop ups just don't seem practical to me.  Are you going to run a macro - if so, then what will the pop up tell you?  Will it be a list of people for a specific certification or just a list of people across all certification?  In any event then you have a msgbox with information.    

I can certainly provide sample code - just followup and tell me that you want that.

Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: I would like a sample that I can edit.  I prefer a pop up "macro" state "DL expires"from column AB with "employee name" column C or "hoist expires" column AG with "employee name" Column C and so on and so forth. And worn of the expiration date 2 weeks prior.  As I have created this but have to make it dummy proof for someone with little to no knowledge of excel! I believe the pop would proof post effective for this.  As someone with little or no knowledge wouldn't know how to filter.


Here is some sample code. I have incorporated the information for the 4 columns you listed in your first post.  It will list names if they are two weeks form expiration.  If you want to list exactly two weeks (14 days) from today then you would change

If cell.Value - Date <= 14 Then


If cell.Value - Date = 14  Then

Sub abc()
Dim sh As Worksheet
Dim vv(1 To 100) As String
Dim v(1 To 4, 1 To 2) As String
Dim r As Range, rr As Range, rc As Range, cell As Range
Dim idx As Long, i As Long, numcol As Long
Dim ss As String

Set sh = Worksheets("Data")
Set r = sh.Range("C2", sh.Cells(sh.Rows.Count, "C").End(xlUp))

v(1, 1) = "DL expires"
v(1, 2) = "AB"
v(2, 1) = "DOT expires"
v(2, 2) = "AD"
v(3, 1) = "MA Hoist expires"
v(3, 2) = "AG"
v(4, 1) = "RI Host expires"
v(4, 2) = "AK"

numcol = UBound(v, 1)

idx = 0
For i = 1 To 4
Set rr = sh.Range(v(i, 2) & ":" & v(i, 2))
Set rc = Intersect(rr.EntireColumn, r.EntireRow)
 idx = idx + 1
 vv(idx) = v(i, 1)
 For Each cell In rc
  If cell.Value - Date = 14 Then
     idx = idx + 1
     vv(idx) = sh.Cells(cell.Row, "C")
  End If

If idx <= numcol Then
 MsgBox "Nothing to report"
 ss = ""
 For i = 1 To idx
   If i <> idx Then
     If InStr(1, vv(i + 1), "expires", vbTextCompare) > 0 And _
        InStr(1, vv(i), "expires", vbTextCompare) > 0 Then
         ' do nothing
       ss = ss & vv(i) & vbNewLine
     End If
      If InStr(1, vv(i), "expires", vbTextCompare) = 0 Then
        ss = ss & vv(i)
      End If
   End If
 MsgBox ss
End If
End Sub

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.