addind a list from msgbox

[filterway]

hello all,

i'm not a vba user but i was able to do some basic command to reach my goal. I have created a command (in the sheet ThisWorkBook) who tells me which bridge need inspection soon (i'm a bridge inspector!) it works fine. At the opening, every time excel find a bridge with inspection date expired, msgbox appears and tell the bridge need inspection as soon as possible .I also have another msbox command and tell the user this bridge need inspection within 2 weeks. My problem is if i have 10 bridges with inspection expired date and bridges needing inspection within 15 days, i have to push 10 time "enter" to finally acces excell sheet.

Is it possible to have the msgbox only twice; one with a list of bridges who need inspection and a second msgbox with a list of bridges who needs inspection within 15 days,?

i need result like this: (P-XXXXX is the number of the bridge found by the command)




P-15452
P-17558
P-18787

these bridges needs inspection as soon as possible



(This ends the first msgbox. Now example of result for the second msgbox)



P-01425
P-18457

These bridges need inspection within 15 days.




Here is the command (i don't know how to call this?!)

Private Sub Workbook_Open()
'to advice bridges needing attention
Dim DATEOFTHENEXTINSPECTION As Range
For Each DATEOFTHENEXTINSPECTION In ActiveSheet.Range("DATE_OF_THE_NEXT_INSPECTION")
value = Cells(DATEOFTHENEXTINSPECTION.Row, 1)
If DATEDOFTHENEXTINSPECTION < Date And Not DATEOFTHENEXTINSPECTION = "" Then
MsgBox "THE BRIDGE " & value & " must be inspected as soon as possible", vbCritical, "inspection delays expired"
Else
End If
If DATEOFTHENEXTINSPECTION >= Date And DATEOFTHENEXTINSPECTION < Date + 16 Then
MsgBox "the bridge " & value & " must be inspected within 15 days", vbExclamation, "bridge inspection is due for this month"
Else
End If
Next
End Sub

It's probably simple but for me it's hard to understand. if you have the answer i'll very appreciate if you can insert the answer in the command lines above.

Thanks a lot

 

[Fluff]

Glad to help & thanks for the feedback

 

[filterway]

There is still one minor problem. When there is no bridges with expired date our near inspection date, the message box appears twice with no bridges number on it. Is there a command (like my original command) i can add to avoid any message box pop-up if there is no bridges require attention?

 

[Fluff]

Simply change the last 2 lines to

   If Not Msg1 = "" Then MsgBox Msg1 & vbLf & " must be inspected as soon as possible", vbCritical, "inspection delays expired"
   If Not Msg2 = "" Then MsgBox Msg2 & vbLf & " must be inspected within 15 days", vbExclamation, "bridge inspection is due for this month"

 

[filterway]

Simply change the last 2 lines to

   If Not Msg1 = "" Then MsgBox Msg1 & vbLf & " must be inspected as soon as possible", vbCritical, "inspection delays expired"
   If Not Msg2 = "" Then MsgBox Msg2 & vbLf & " must be inspected within 15 days", vbExclamation, "bridge inspection is due for this month"

Great! thank you again

 

[Fluff]

My pleasure

 

[filterway]

Hello All,

since i wrote my question above, 2 years as passed... and i'm unable to remember how i did it. I think i had to define something in excel but i don't remember the name of the command. it's about this line: value = Cells(DATEOFTHENEXTINSPECTION.Row, 1) What is the command in excel to set the name "DATEOFTHENEXTINSPECTION"? it's about the name of the column i think.

thank you.

p.s. the macro doesn't work now. it block at this line: For Each DATEOFTHENEXTINSPECTION In ActiveSheet.Range("DATE_OF_THE_NEXT_INSPECTION")

 

[Fluff]

Do you have a named range called "DATE_OF_THE_NEXT_INSPECTION" ?