VBA - Dialog box in conjunction with highlighted row

[PierreT]

Good day everyone,
I’m looking for some assistance building a macro that would trigger a Dialog Box when TODAY’s date is 90 days shy (before) of the expiry date, then 45, and finally the day of. Also, I would like to highlight the row of that date with a specific color depending how close it is from the expiry date. Finally, the macro would apply to the entire workbook and not only on the active sheet.

At 90 days before expiry:

• A dialog box with the name of the worksheet as the title (with an “OK” button).
• As for the text of the dialog box I would like to have the text of Column A for that respective date.
• At the same time, I would like to be able to highlight the row yellow for that specific date.

At 45 days before expiry: Same as above except for the highlighted color, which would be orange.

The day of the expiry: Same as above except for the highlighted color, which would be red.

The name and expiry date of the 6 first worksheets are written in column A and B respectively.
The name and expiry date of the 7th worksheet sheet are written in column A and C respectively.
The name and expiry date of the 8th worksheet sheet are written in column A and D respectively.

My department prohibits any downloads on the computer, hence I’m unable to use Xl2BB. I’ve attached a picture of the workbook instead.

I only took 1 year of VBA in high school many years ago. In order to better myself I wouldn’t mind if you could provide some explication with the code.

Thank you very much for your assistance on this matter.

 

[Micron]

I imagine for what you want you'd do these checks on workbook open? If wb stays open for over a day, this can be problematic since you chose exact number values such as 90. If on Monday a calculation is 89 days and I leave the wb open until closing time on Tuesday, when I open wb on Wednesday it will never equal 90 days. The only way around that would be to use some other event such as wb calculate or perhaps a timer event.

Maybe if you post the code you tried it would give responders a starting point.

 

[PierreT]

I imagine for what you want you'd do these checks on workbook open? If wb stays open for over a day, this can be problematic since you chose exact number values such as 90. If on Monday a calculation is 89 days and I leave the wb open until closing time on Tuesday, when I open wb on Wednesday it will never equal 90 days. The only way around that would be to use some other event such as wb calculate or perhaps a timer event.

Maybe if you post the code you tried it would give responders a starting point.

Good day and thank you for looking at my issue. Ideally, It would be helpful if the formula could apply regardless of the document being opened or closed.

As I mention, I'm not too good with VBA. I've done some research but I'm afraid it it too far advanced for my skills.

Private Sub Workbook_Open()
Dim x As Integer
For x = 4 To 50
If Cells(x, 2).Value <= Date Then
MsgBox "Nom: " & Range("A4").Value, vbOKOnly + vbExclamation, Range("$A1").Value
End If
Next x
End Sub

First thing first, when the dates in the range of B4:B50 are smaller or equal then today (I do not know how to specify 90, 45 days before and the day of) I would like the msgbox to appear. Instead of having the fixed value of A4 in the msgbox, I would like to have the value of the specific row (Column A, 4 to 50) where the date is 90, 45 days before and/or the day of (does that make sense?)

As for the highlight part, I was able to do it using CF but it is quite messy. Is there a way I can include it in the code?

 

[Micron]

You will need a decision block, such as Select Case where one action is taken if the date difference is 90 or less, 45 or less, or equal to. If these decisions go in ascending order (equal or later first then 60 then 90) it should work. Yes CF can be done at the same time.

BTW, the message box is probably not useful? If there are 3 cells and each one of them is to be a different colour, then what? 3 messages? One general message advising that there are issues? Why go to the trouble of coding for a message that contains the row numbers when you're identifying them with colour? That would add to the complexity.

As you say, this can all be done with cf (except it won't raise a message box) which ought to be simpler, so why didn't you like the result you got?

 

[PierreT]

You will need a decision block, such as Select Case where one action is taken if the date difference is 90 or less, 45 or less, or equal to. If these decisions go in ascending order (equal or later first then 60 then 90) it should work. Yes CF can be done at the same time.

BTW, the message box is probably not useful? If there are 3 cells and each one of them is to be a different colour, then what? 3 messages? One general message advising that there are issues? Why go to the trouble of coding for a message that contains the row numbers when you're identifying them with colour? That would add to the complexity.

As you say, this can all be done with cf (except it won't raise a message box) which ought to be simpler, so why didn't you like the result you got?

The msgbox is only to notice me that somewhere in the workbook, there is a due date approaching. Instead of clicking on all sheets, the name of the sheet would be mentioned in the msgbox title as well as the name of that person (Column A).

However, after further consideration, you are right when saying there would be too many msgbox popping-up. So I will take your advice and drop the msgbox. I will only focus on highlighting the cells. For learning perspective, could you show me how to highlight a row in VBA based on the date in Column B, with the 90, 45 and the day of.

After searching on the web, this is what I came up with:

Private Sub Worksheet_Activate()
Dim cell As Range

For Each cell In Range("B4:B50")
If cell.Value <= Date - 90 Then
cell.Offset(0, 0).Interior.ColorIndex = 6

ElseIf cell.Value <= Date + 45 Then
cell.Offset(0, 0).Interior.ColorIndex = 46

ElseIf cell.Value = Date Then
cell.Offset(0, 0).Interior.ColorIndex = 3

ElseIf cell.Value = "" Then
cell.Offset(0, 0).Interior.ColorIndex = x1none

End If
Next cell
End Sub


However, the formula doesn't appear to work.

Any thoughts?

 

[Micron]

Because in that code at first you're asking if the cell date value is less than 90 days in the past (so older than 90 days ago) and in the second, if cell date is less than 45 days into the future. I thought you wanted to highlight if cell value was within Date + 30, 45 and 90 into the future?

when TODAY’s date is 90 days shy (before) of the expiry date,

You should post some examples to make this clearer. Suggest just type some dates into a few cells of any sheet. I col A put an example of "today's" date whatever that is, in a few rows. In B put dates - 1 or 2 that don't match anything, 1 or 2 that for each of yellow, orange and red. Copy that range and just paste into a post. In the meantime I'll pm you with a plan on how to do this that I think you'll like.

 

[Micron]

BTW, this needs clarification

The name and expiry date of the 6 first worksheets are written in column A and B respectively.
The name and expiry date of the 7th worksheet sheet are written in column A and C respectively.
The name and expiry date of the 8th worksheet sheet are written in column A and D respectively.

Are they named as numbers? As Sheet1, ...Sheet8?
Are you referring to sheet names or code names? Sheet names are in parentheses, like (Data). Code names are not, so in the sheets list it looks like
Sheet6 (Data)