Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

pop up message horizantal based on condition

0

hi

I  would  showing  data  based  on  condition in COL L    <= today    but  horizantal 

so   it  should   show  like  this   when open file 

ITEM          ID               CODE NO          CLASSIFICATION    QTY              DATE 

     1              ID100        BSCD-1                  SERVICE1            250                8/2/2021

     2             ID104        BSCD-5                  SERVICE 5             500                8/3/2021

     3              ID108        BSCD-9                   SERVICE9             50                8/8/2021

this  is  the  current macro

Private Sub Workbook_Open()
    Dim lstRow As Long
    Dim i As Long
    Dim msg As String

    With Worksheets("SHEET1")

    lstRow = .Range("A" & .Rows.Count).End(xlUp).Row

    For i = 2 To lstRow

        If .Range("L" & i) <= Today Then
            msg = msg & .Range("B" & i) & vbCr & .Range("C" & i) & vbCr & .Range("D" & i) & vbCr & .Range("E" & i) & vbCr & .Range("l" & i) & vbCr
        End If
    Next i
    End With

    If Len(msg) > 0 Then
    MsgBox "The recorded data are" & _
    vbCrLf & msg, 64, "Notification"
    End If
End Sub
excel
MS Office Version: 2019
posted by: Alaa (rep: 28) on Sun Aug 8, 2021 at 12:24 pm
Answer
Discuss

Answers

0
Selected Answer

Alaa

Your test should read:

   If .Range("L" & i) <= Date Then
            msg = msg & .Range("B 
since Today is not a VBA function (though it is a worksheet function). Today is therefore seen by your code as an undeclared variable (value 0) which means no legitimate date numbers will be less than it!) 

You could use the following (especially if non-dates or blanks are expected in column L):

If IsDate(.Range("L" & i)) And .Range("L" & i) <= Date Then
since the added part in bold will ignore anything whjich is not a date.

When creating the variable msg, you could replace VbCR (carriage return) with vbTab (tab) in each case except the last.

Those changes (and other minor errors in your code) are in bold below and within the attached revised file:

Private Sub Workbook_Open()

Dim lstRow As Long
Dim i As Long
Dim msg As String

With Worksheets("SHEET1")

    lstRow = .Range("A" & .Rows.Count).End(xlUp).Row

    For i = 2 To lstRow

        If If IsDate(.Range("L" & i)) And .Range("L" & i) <= Date Then
            msg = msg & .Range("B" & i) & vbTab & .Range("C" & i) & vbTab & .Range("D" & i) & vbTab & .Range("E" & i) & vbTab & .Range("L" & i) & vbCr
        End If
    Next i
End With

If Len(msg) > 0 Then
MsgBox "The recorded data are" & vbCr & msg, 64, "Notification"
End If

End Sub
For a more controlled way, consider using a VBA ListBox for the output rather than a MsgBox (message box). 
posted by: John_Ru (rep: 6142) on Sun Aug 8, 2021 at 2:35 pm
Discuss

Discussion

thanks but  I  no  know   why  show  all  of  data  despite   of  I  specified condition   this 
If .Range("L" & i) <= Today Then
Alaa (rep: 28) Aug 8, '21 at 3:49 pm
Alaa.
See my revised Abswer for an explsnation/ solution. Sorry I'm away from my PC and  reading your code on my phone (so overlooked the obvious error). 
John_Ru (rep: 6142) Aug 8, '21 at 5:05 pm
thanks   again    but  the   same   problem  nothing  changes 
Alaa (rep: 28) Aug 8, '21 at 6:17 pm
Alaa
Did you try the full revised code from my abswer? It worked on my PC (but with just your sampke data) 
John_Ru (rep: 6142) Aug 8, '21 at 6:56 pm
     yes  I  did. it's  very  stange  if  work for  you .
Alaa (rep: 28) Aug 8, '21 at 11:35 pm
Alaa

I've replaced your macro with mine- please see revised file in Answer.

I added several more dates in column L to show that the revised test works.

I saw that the Microsoft guidance here on the Date function implies Date$ might be used if the system calendar is in HijriI. That returns a string like "08-09-2021" so your test might be against something like DateValue(Date$) rather than Date (but I haven't had time to test what works so have NOT made that change).
John_Ru (rep: 6142) Aug 9, '21 at 1:14 am
youre answering shows   data  based on condition  but  the  problem   if  some  cells are  empty  in COL L  . I mean  there  is  no  date  at all .  it  will also  show  but  it supposses  not  showing  data don't  contain  date in COL L   . about the system calendar is  not in   HijriI
Alaa (rep: 28) Aug 9, '21 at 4:12 am
Alaa. I'm on my phone again but recall that I first tried the macro with blank cells in L and it worked fine. 

If you have problems, you could make the test:
If IsDate(.Range("L" & i)) And .Range("L" & i) <= Date Then
John_Ru (rep: 6142) Aug 9, '21 at 5:25 am
finally it works excellently . thanks for track my problem and answer me.
have a good day !
Alaa (rep: 28) Aug 9, '21 at 8:42 am
Alaa

Thanks for the feedback and for selecting my Answer.

Note that I've modified my answer to explain the new test and I've attached a new file.
John_Ru (rep: 6142) Aug 9, '21 at 11:47 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login