VBA custom msgbox alert

Question

Hello,

I created the following Macro in order to have a msgbox alerting on the date of expiry of a list of contracts (each line with a "end date" expired or that will expire in the next 6 months will be shown on the msgbox). The macro works perfectly, the problem I have with this code is that the standard excel msgbox is not large enough to show all the lines (for instance, if have more than 13 lines expired, I will not see the 14 line and ..., which is a big problem).

I would like to use a "custom" msgbox to show the result of the macro (for instance a "ListBox" form control could be perfect for that - all the data will be in the Listbox, and I will be able to scroll down to show all the other lines), but I don't know how to do that.

The other workaround or acceptance solution could be to replace the "Msgbox" by the creation of a code that will send a list of all the contracts/lines that will expire by email but I suppose, that's probably more complex (need to call components/libraries outside Excel).

Would it be possible to help me in order to improve this code.

Thank you in advance and do not hesitate if you need more information.

/////////////////////////////////////////////////

Module1

*********************

Sub Échéance_Contrats()

Dim C As Range, X As String

X = vbTab & vbTab

Application.ScreenUpdating = False

With Sheet1

    .ListObjects("Table1").Range.AutoFilter Field:=5, Operator:= _

        xlFilterValues, Criteria1:="<=" & _

        DateSerial(Year(Date), Month(Date) + 6, Day(Date)) * 1

        With .Range("e2:E" & .Range("E65536").End(xlUp).Row)

            For Each C In .SpecialCells(xlCellTypeVisible)

                If UCase(C.Offset(, 1)) = "YES" Then

                    With C

                        Message = Message & .Offset(, -4) & X & _

                             .Offset(, -3) & " - " & "Owner: " & _

                             .Offset(, 7) & " - " & "End Date: " & _

                             C & "" & vbCrLf

                    End With

                End If

            Next

        .AutoFilter

        End With

End With

Application.ScreenUpdating = False

If Message <> "" Then

    MsgBox Message, , "Attention préavis contrat(s): "

End If

End Sub

/////////////////////////////////////////////////

ThisWorkbook

*********************

Private Sub Workbook_Open()

Échéance_Contrats

End Sub

/////////////////////////////////////////////////

Excel file with the Macro

***************************

http://cjoint.com/?0DolJYInPqU

Answer 1

I found a solution:

1. Module1

I replaced this line of code:

    MsgBox Message, , "Attention préavis contrat(s): "

By:

    UserForm1.TextBox1.Value = Message

    UserForm1.Show

2. I created a "UserForm1" in the MBA consolde with this code

    Private Sub UserForm_Initialize()

   TextBox1.MultiLine = True

   TextBox1.ScrollBars = fmScrollBarsVertical

   End Sub

If you know some tips to improve text formatting on the textbox, I am always interested to continue improving this Excel tool.