Having issues with pop-up alert in excel Visual Basic

0 votes

I tried to put up a code so that when a reagent expires in 7 days, a message alert pops up, and when the workbook opens, the reagent is expired. The out-of-date reagent needs to be mentioned in the message. I tried to make the code work only for the "FA Reagents" (A4:A20) and the expiration dates for those reagents (C4:C20), but I finally want the code to work for every reagent on this page.

Excel Sheet

Private Sub Workbook_Open()
    Dim ws As Worksheet
    Dim rReagents As Range
    Set rReagents = Range("A4:A20")
    Dim rExpiration As Range
    Set rExpiration = Range("C4:C20")
    Dim lLastrow As Long, i As Long
    Set ws = Worksheets("Reagent-Equipment")
    lLastrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    With ws
        For i = 2 To lLastrow
            If .Cells(i, 2) = Date + 7 Then MsgBox ("Reagent expiring in 7 days for " & .Cells(i, 1))
            If .Cells(i, 2) = Date Then MsgBox ("Reagent expiring today for " & .Cells(i, 1))
        Next
    End With
End Sub

This is the code I tried, but I can't notification to pop up when the workbook opens when a reagent is expired or expired within 7 days.

Feb 4, 2023 in Others by Kithuzzz
• 38,000 points
673 views

1 answer to this question.

0 votes

You may find the following code of interest:

Private Sub Workbook_Open()

    Dim myShortDate As String
    Dim myExpired As String
    Dim ws As Worksheet
    Set ws = Worksheets("Reagent-Equipment")
    
    
    Dim myReagents As Variant
    myReagents = Application.WorksheetFunction.Transpose(ws.Range("A4:A20").Value)
    
    Dim myExpiry As Variant
    myExpiry = Application.WorksheetFunction.Transpose(ws.Range("C4:C20").Value)
   
  
    Dim myIndex As Long
    myIndex = 1
    Dim myItem As Variant
    For Each myItem In myReagents
    
        If Now > VBA.CDate(myExpiry(myIndex)) Then
        
            If VBA.Len(myExpired) = 0 Then
                
                myExpired = vbTab & myReagents(myIndex)
            
            Else
            
                myExpired = myExpired & vbCrLf & vbTab & myReagents(myIndex)
                
            End If
        
        ElseIf Now + 7 > VBA.CDate(myExpiry(myIndex)) Then
        
            If VBA.Len(myShortDate) = 0 Then
                
                myShortDate = vbTab & myReagents(myIndex) & " on " & myExpiry(myIndex)
            
            Else
            
                myShortDate = myShortDate & vbCrLf & vbTab & myReagents(myIndex) & " on " & myExpiry(myIndex)
                
            End If
            
        End If
    
        myIndex = myIndex + 1
        
    Next
    
    If VBA.Len(myExpired) > 0 Then
    
        myExpired = "Expired Reagents" & vbCrLf & vbCrLf & myExpired & vbCrLf & vbCrLf
        
    End If
    
    
    If VBA.Len(myShortDate) > 0 Then
    
        myShortDate = "Reagents due to expire " & vbCrLf & vbCrLf & myShortDate & vbCrLf & vbCrLf
        
    End If
    
    Dim myMessage As String
    myMessage = myExpired & myShortDate
    
    If VBA.Len(myMessage) > 0 Then
    
        MsgBox myMessage, vbCritical
        
    End If
    

End Sub
answered Feb 4, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,690 points
991 views
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 63,600 points
1,396 views
0 votes
1 answer

How to create a drop-down in excel with custom values

You can accomplish that using code rather ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
• 63,600 points
1,119 views
0 votes
1 answer

Sort Excel worksheets based on name, which is a date

Sorting sheets of a workbook are rather ...READ MORE

answered Oct 17, 2022 in Others by narikkadan
• 63,600 points
563 views
0 votes
1 answer

How can I stop my vba code from giving me an error 424?

Object Variables in Loops The main issue was ...READ MORE

answered Feb 9, 2023 in Others by narikkadan
• 63,600 points
909 views
0 votes
1 answer

Runtime Error when trying to Loop Worksheets

Copy To Multiple Worksheets A Quick Fix Option Explicit Sub ...READ MORE

answered Mar 19, 2023 in Others by Kithuzzz
• 38,000 points
483 views
0 votes
1 answer

Export multiple worksheets without formula with the ability to select exact sheets and location

Try this: Sub ExportSheets() Dim ...READ MORE

answered Mar 24, 2023 in Others by Kithuzzz
• 38,000 points
553 views
0 votes
1 answer

Can I show a formula result in a pop-up window in Microsoft Excel?

Yes, you can display the formula result—that ...READ MORE

answered Nov 12, 2022 in Others by narikkadan
• 63,600 points
750 views
0 votes
1 answer

Using Visual Basic to pull data from within a range to use in an Excel function

Use AVERAGEIFS instead of the full range. ...READ MORE

answered Jan 14, 2023 in Others by narikkadan
• 63,600 points
515 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP