addind a list from msgbox

filterway

New Member
Joined
Dec 19, 2017
Messages
10
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,370
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to the board.
Untested but try
Code:
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

New Member
Joined
Dec 19, 2017
Messages
10
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

New Member
Joined
Dec 19, 2017
Messages
10
i forgot to mention it list all the bridges twice, one for expired date and one for inspection within 15 days.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,370
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Oops, made a typo. Make the change in red to this
Code:
         If Msg2 = "" Then
            Msg2 = "THE BRIDGE " & ValU
         Else
            Msg2 = Msg[COLOR=#ff0000]2[/COLOR] & vbLf & ValU
         End If
 

filterway

New Member
Joined
Dec 19, 2017
Messages
10
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,370
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Did your code originally work for the overdue inspections?
 

filterway

New Member
Joined
Dec 19, 2017
Messages
10
wooooot woooot!!! :cool: yes it works! Thank you very much!! it's a big improvment and my teammates will certainly appreciate.

thanks again! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,480
Messages
5,601,911
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top