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]

Hi & welcome to the board.
Untested but try

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

 

[filterway]

Hi Fluff and thank you for your quick reply!

it works but half way... it list all the bridge number, not only the ones with expired dates or dates within 15 days. is it possible to send picture of result on the forum?

 

[filterway]

i forgot to mention it list all the bridges twice, one for expired date and one for inspection within 15 days.

 

[Fluff]

Oops, made a typo. Make the change in red to this

         If Msg2 = "" Then
            Msg2 = "THE BRIDGE " & ValU
         Else
            Msg2 = Msg[COLOR=#ff0000]2[/COLOR] & vbLf & ValU
         End If

 

[filterway]

we approaching the goal!!! it works for bridges within 15 days but list all the bridges number for expired date. i'll try to see why one works and not the other one...

 

[Fluff]

Did your code originally work for the overdue inspections?

 

[filterway]

Did your code originally work for the overdue inspections?

Yes it works.

everything looks similar between msg1 and msg2..

 

[Fluff]

Got it, you've got a typo in this line

if DATE[COLOR=#ff0000]D[/COLOR]OFTHENEXTINSPECTION < Date And Not DATEOFTHENEXTINSPECTION = "" Then

I'd recommend running Option Explict, which helps to spot errors like this.
Have a look here for more about it
http://www.excel-easy.com/vba/examples/option-explicit.html

 

[filterway]

wooooot woooot!!! yes it works! Thank you very much!! it's a big improvment and my teammates will certainly appreciate.

thanks again!