pop up message all of data contain date when open file

[Mussa]

hi
I search for macro when open file . it pop up message show all of data based on col M contains date
so in my case it should show the rows 2,6,10

REP1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ITEM	BATCH NO	CODE NO	NAME	AMOUNT	NOTES							DATE
2	1	BT-00	CD-00	ALI1	250	RECORDED							03/08/2021
3	2	BT-01	CD-01	ALI2	200	NOT RECORDED
4	3	BT-02	CD-02	ALI3	300	NOT RECORDED
5	4	BT-03	CD-03	ALI4	400	NOT RECORDED
6	5	BT-04	CD-04	ALI5	500	RECORDED							05/08/2021
7	6	BT-05	CD-05	ALI6	120	NOT RECORDED
8	7	BT-06	CD-06	ALI7	300	NOT RECORDED
9	8	BT-07	CD-07	ALI8	120	NOT RECORDED
10	9	BT-08	CD-08	ALI9	50	RECORDED							07/08/2021
11	10	BT-09	CD-09	ALI10	120	NOT RECORDED
1

the messgae should be like this

ITEM	BATCH NO	CODE NO	NAME	AMOUNT	NOTES	DATE
1	BT-00	CD-00	ALI1	250	RECORDED	03/08/2021​
2	BT-04	CD-04	ALI5	500	RECORDED	05/08/2021​
3	BT-08	CD-08	ALI9	50	RECORDED	07/08/2021​

 

[Crystalyzer]

this assumes that there is nothing in columns G-L that you want in the pop-up

 

[Mussa]

post#11 this is exactly what I want

 

[Crystalyzer]

You will need to create a Userform called UserForm1 and place a listbox called ListBox1 and a command button called CommandButton1 on the form so it looks like the below

The following settings should be changed
UserForm1
Height = 278.25
Width = 455.25

ListBox1
Height = 200
Width = 430

Once this is done, right click on the userform and select "View Code". Copy the code below into the UserForm1 vbe

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    Load UserForm1
    With ListBox1
        .ColumnCount = 13
        .ColumnWidths = "1 cm;2 cm;2 cm;2 cm;2 cm;3 cm;0;0;0;0;0;0;3 cm"
        .List = ShowDateData()
        .ListIndex = 0
    End With
    With UserForm1
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    End With
End Sub

Private Function ShowDateData() As Variant
    Dim arr() As Variant
    Dim arr2() As Variant
    Dim lRow As Long, i As Long, j As Long, k As Long, arr2Rows As Long
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    k = 0
    
    With ws
        lRow = .Cells(.Rows.Count, "M").End(xlUp).Row
        arr2Rows = WorksheetFunction.CountA(.Range("M:M"))
        arr = .Range("A1:M" & lRow)
        ReDim arr2(arr2Rows - 1, UBound(arr, 2) - 1)
        For i = 1 To UBound(arr, 1)
            If arr(i, UBound(arr, 2)) <> "" Then
                For j = 0 To UBound(arr, 2) - 1
                    arr2(k, j) = arr(i, j + 1)
                Next
                k = k + 1
            End If
        Next i
    End With
        
    ShowDateData = arr2
End Function

To run this code when the workbook is open place this code in the Workbook Open section of the "ThisWorkbook" vbe

Private Sub Workbook_Open()
    UserForm1.Show
End Sub

 

[Mussa]

it gives error subscript out of range in this line

UserForm1.Show

Private Sub Workbook_Open()
UserForm1.Show
End Sub

 

[Crystalyzer]

Please create a test macro by placing this code in the vbe

Sub Test()
UserForm1.Show
End Sub

then debug and step through the code until you get the error and let me know the code that it stops on.

 

[Mussa]

the same error

UserForm1.Show

 

[Crystalyzer]

Did you step through the code in debug mode? or just run it?

 

[Crystalyzer]

You will have to show the "debup" toolbar in the vbe and click the "Step Into" icon see picture below. Continue clicking the icon to step through each line of the code until the error appears.

 

[Fluff]

In the VB Editor goto Tolls, Options, General & then check the box next to "Break in class module" that way you should get to see what is causing the error.

 

[Crystalyzer]

@Fluff BRILLIANT! Thanks for the lesson!