pop up message all of data contain date when open file

Mussa

Board Regular
Joined
Jul 12, 2021
Messages
225
Office Version
  1. 2019
  2. 2010
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
ABCDEFGHIJKLM
1ITEMBATCH NOCODE NONAMEAMOUNTNOTESDATE
21BT-00CD-00ALI1250RECORDED03/08/2021
32BT-01CD-01ALI2200NOT RECORDED
43BT-02CD-02ALI3300NOT RECORDED
54BT-03CD-03ALI4400NOT RECORDED
65BT-04CD-04ALI5500RECORDED05/08/2021
76BT-05CD-05ALI6120NOT RECORDED
87BT-06CD-06ALI7300NOT RECORDED
98BT-07CD-07ALI8120NOT RECORDED
109BT-08CD-08ALI950RECORDED07/08/2021
1110BT-09CD-09ALI10120NOT RECORDED
1

the messgae should be like this
ITEMBATCH NOCODE NONAMEAMOUNTNOTESDATE
1BT-00CD-00ALI1250RECORDED
03/08/2021​
2BT-04CD-04ALI5500RECORDED
05/08/2021​
3BT-08CD-08ALI950RECORDED
07/08/2021​
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
This gets you MOST of the way there. the code below will retrieve the data you want and put it in a msgbox, however, the msgbox has a limitation and the data wraps. Also, I'm not sure what would happen if you had more rows that could fit. There might be a character limit to the msgbox. I am looking into using a listbox on a user form as the "pop up" but thought I would give you what I have so far.

VBA Code:
Sub ShowDateData()
    Dim arr() As Variant
    Dim arr2() As Variant
    Dim rng As Range
    Dim lRow As Long, i As Long, j As Long, k As Long, arr2Rows As Long
    Dim ws As Worksheet
    Dim strMsg As String, strTmp As String
    Dim uf As UserForm
    
    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
    
    For i = 0 To UBound(arr2, 1)
        For j = 0 To UBound(arr2, 2)
            If arr2(i, j) <> "" Then
                strTmp = strTmp & arr2(i, j) & vbTab
            End If
        Next j
        If i = 0 Then
            strMsg = strTmp
        Else
            strMsg = strMsg & vbCrLf & strTmp
        End If
        strTmp = ""
    Next i
    
    MsgBox strMsg
End Sub
 
Upvote 0
the msgbox has a limitation and the data wraps

what you mean ? does the code crashes if I have a big data ?

I tested your code but two COLS are not arranged (NOTE & DATE) should show next to COL AMOUNT
see the picture


View attachment 44353
 
Upvote 0
ms.PNG
 
Upvote 0
That's what I meant about the msgbox having limitations. It wraps the text and cannot extend any wider.
 
Upvote 0
I've been looking into a user form with a listbox but I haven't gotten very far.
 
Upvote 0
I think I solved the issue, however, I need to know if there is data in columns G through L and if you want that data included or not.
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

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
Back
Top