Popup menu with count

[Closed]
Report
-
Posts
1316
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 21, 2021
-
Hello,
Can you help me with this?
I have list of 150 driver's with licence valid upto info in D3:D153. I am trying following things
1. Check if validity expires in 3 month's.
2. If yes popup message on file open like alert that x no licence are expiring in next 3 months.
Thanks in advance
Ajit


3 replies

Posts
1316
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 21, 2021
237
Hello Ajit,

Do you mean all licences that expire in 90 days' time or do you mean all licences that expire between today and 90 days' time?

Cheerio,
vcoolio.
Hello Vcoolio
Yes i meant all licences that expire between today and 90 days' time?

Regards
Posts
1316
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 21, 2021
237
Hello Ajit,

This code may help you then:-
Private Sub Workbook_Open()

Application.ScreenUpdating = False

   Dim Total As Integer

For Each cell In Range("C4:C15")
    Total = Application.WorksheetFunction.CountIf(Range("C4:C15"), "<=90")
    If cell.Value <= 90 Then
    Range(Cells(cell.Row, "A"), Cells(cell.Row, "C")).Interior.ColorIndex = 8
End If

Next

For Each cell In Range("C4:C15")
   If cell.Value = 0 Then
   Range(Cells(cell.Row, "A"), Cells(cell.Row, "C")).Copy
   Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
   Range(Cells(cell.Row, "A"), Cells(cell.Row, "C")).ClearContents
   Range(Cells(cell.Row, "A"), Cells(cell.Row, "C")).Interior.ColorIndex = xlNone
End If

Next

MsgBox "The number of licences expiring within the next 90 days = " & Total
Application.ScreenUpdating = True
Application.CutCopyMode = False

End Sub


The code is a Work_Book Open event and needs to be placed in the Work Book module as it will trigger as soon as the work book is opened.

Here is my test work book for you to have a look at:-

https://www.dropbox.com/s/21nrxi5t6172zn0/Ajit%20Singh%282%29.xlsm?dl=0

Just to explain what the code does (once the work book is opened):-

- As you can see in the test work book, I have just three columns of fictitious data (as I don't know how your work book is set out) with headings on Row 3 and today's date in Cell A1. For the sake of the exercise, data goes down eleven rows only. The code relies on there being a date (=TODAY()) somewhere on your work sheet.

- Column C (Days to Expiry) has the DATEDIF formula dragged down as far as needed (click on a cell in Column C to see the formula in the function bar). This formula calculates the number of days between now and the Licence Due Date in Column B. Once the day count is between now and 90 days' time, a message box will pop up and tell you how many licences are due to expire within this time. This will include any cells with a zero value as a cell with a zero value is, obviously, due TODAY(). The rows with licences due to expire will also be high-lighted.

- Once the day count in Column C reaches zero, the row with the data associated with the zero value will be transferred to Sheet 2 so you can deal with it as you see fit. This row of data will also be deleted from Sheet 1. If you don't need this function, it can be deleted.

You will have to change the cell and sheet references in the code to suit your own work book.

I hope that this helps.

Cheerio,
vcoolio.