You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Pop up in Excel 2013

Advertisement

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.

--

Regards,

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.

Heather,

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

to

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

Next

Next

If idx <= numcol Then

MsgBox "Nothing to report"

Else

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

Else

ss = ss & vv(i) & vbNewLine

End If

Else

If InStr(1, vv(i), "expires", vbTextCompare) = 0 Then

ss = ss & vv(i)

End If

End If

Next

MsgBox ss

End If

End Sub

--

Regards,

Tom Ogilvy

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | Wow this is huge! I would have never been able to figure this out from watching tutorials! Thanks you very much for your help and patience! I will give it a shot! |

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

Excel

Answers by Expert:

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. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.