Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: VBA popup box warning of expiration date

  1. #1
    New Member
    Join Date
    Jan 2015
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA popup box warning of expiration date

    Hello guru's! I'm trying to get a popup window to show when a date is entered that is less than 8 months from today's date.
    I also need it to send out the alert when the spreadsheet is opened.
    Basically, Column A is the part number, Column B is the lot number and Column C is the expiration date. If the user enters a date into column C (which is currently 57 lines long, but could be as many as 200) and it's less than 8 months out, it displays "Part A, Lot B is within the 8 month requirement". They should be able to close the window out in case it's going to be accepted anyway. Therefor, once the workbook is opened again, it needs to check for expiration dates that are already populated for the same criteria and display a list of parts/lots that are out of tolerance.
    And just because I can't make it simple, if the expiration date doesn't have anything entered, it shouldn't throw out the warning, just move on to the next populated cell.

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    12,625
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA popup box warning of expiration date

    Can you please help me understand:
    less than 8 months from today's date.

    So are you saying 5/17/2014 would be:
    less than 8 months from today's date.

    Give me a example of what would and would not apply
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  3. #3
    New Member
    Join Date
    Jan 2015
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA popup box warning of expiration date

    For example, lets say the spreadsheet is opened on 7/1/2018. If the expiration date entered is 3/1/2019 or earlier, it's below the 8 month threshold.

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    12,625
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA popup box warning of expiration date

    Well this part should do the first part of what you want.
    If a date is entered in column C that is out of date you will get a message box.

    This is an auto sheet event script
    Your Workbook must be Macro enabled
    To install this code:
    Right-click on the sheet tab
    Select View Code from the pop-up context menu
    Paste the code in the VBA edit window

    The script will activate when you enter a value in column C
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    'Modified 6/19/18 10:00 PM EDT
    If Not Intersect(Target, Range("C:C")) Is Nothing Then
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
        If Target.Value < DateAdd("M", "8", Date) Then
            ans = "Part Number " & Target.Offset(, -2).Value
            anss = "Lot Number " & Target.Offset(, -1).Value
            MsgBox ans & vbNewLine & anss & vbNewLine & "Is within the 8 month requirement"
        End If
    End If
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  5. #5
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    12,625
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA popup box warning of expiration date

    Now this part I do not understand:
    They should be able to close the window out in case it's going to be accepted anyway.
    Close what window. This is a message Box window.
    You must click OK to continue.
    If your not wanting a message Box then i don't know what you mean when you say Pop up Window.

    Then you said:
    Therefor, once the workbook is opened again, it needs to check for expiration dates that are already populated for the same criteria and
    display a list of parts/lots that are out of tolerance.



    Display a list where?

    See I always need specific details. Saying display a list is not specific.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  6. #6
    New Member
    Join Date
    Jan 2015
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA popup box warning of expiration date

    Sorry, I didn't even think about the user having to press "OK" to release the message box, so that'll work just fine. For now I can live without the list, but what I was saying that if the user has entered 30 parts and populated 30 expiration dates in column C (let's say 6 were within the 8 month window), then when the workbook is opened again (in this case by another user), it will display the 6 parts that are within the 8 month window.
    That said, I pasted your code to try it out and nothing at all happened...

  7. #7
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    12,625
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA popup box warning of expiration date

    So your saying you entered a date like 8/8/2018 into column C and nothing happened.
    And you installed the code like I mentioned:
    Like this:
    This is an auto sheet event script
    Your Workbook must be Macro enabled
    To install this code:
    Right-click on the sheet tab
    Select View Code from the pop-up context menu
    Paste the code in the VBA edit window
    This part of the script only runs when you enter a date like 8/8/2018 into column C
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  8. #8
    New Member
    Join Date
    Jan 2015
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA popup box warning of expiration date

    Yes sir, exactly as you instructed.
    https://www.dropbox.com/s/u23r6ko554...MPLE.xlsm?dl=0

  9. #9
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    12,625
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA popup box warning of expiration date

    I never click on links or open other workbooks or files.

    Try this and tell me what happens when you enter any value in column C

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    'Modified 6/20/18 10:15 AM EDT
    If Not Intersect(Target, Range("C:C")) Is Nothing Then
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
        MsgBox "This works"
    End If
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  10. #10
    New Member
    Join Date
    Jan 2015
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA popup box warning of expiration date

    Every value in C now brings the pop up "This Works"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •