Popup menu with count
Closed
ajit singh
-
Apr 11, 2015 at 04:52 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Apr 16, 2015 at 05:36 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Apr 16, 2015 at 05:36 AM
Related:
- Popup menu with count
- Send popup message to another computer on network windows 10 - Guide
- Show volume in menu bar mac - Guide
- Proline boot menu key ✓ - Windows Forum
- Huawei test menu - Guide
- Count sentences ✓ - Programming Forum
3 responses
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Apr 11, 2015 at 09:36 AM
Apr 11, 2015 at 09:36 AM
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.
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.
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Apr 16, 2015 at 05:36 AM
Apr 16, 2015 at 05:36 AM
Hello Ajit,
This code may help you then:-
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.
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.
