Help with popup boxes

tenaciousjacket · Mar 21st 2021

Hi all,

I'm using the following to build a popup box that prompts me when something is expired (from another thread)

Code

Private Sub Workbook_Open()
Dim i As Integer, expire As String
For i = 4 To [D65536].End(xlUp).Row
If (Cells(i, 4).Value - Date) <= 10 And (Cells(i, 4).Value - Date) >= 0 Then
expire = expire & "- " & i & vbCrLf
End If
Next
MsgBox "Items on the following rows are due to expire:" & vbCrLf & vbCrLf & expire & vbCrLf & "Please action.", vbInformation
End Sub

But I can only get it to work when the expiry dates are in the D column. When I change the For i = 4 To [D65536].End(xlUp).Row to For i = 4 To [E65536].End(xlUp).Row it just ends up not working properly. How would I change the above to reflect a change in where I want to submit the expiring column?

Many thanks in advance!

**Carim** · Mar 21st 2021

Hello and Welcome to the Forum

Below is your macro with comments to explain the logic :

Code

Private Sub Workbook_Open()
Dim i As Integer, expire As String
' Loop all Rows starting at Row Number 4
For i = 4 To [D65536].End(xlUp).Row
' Cells (i,j) uses i for the Row and j for the Column
' so should you need to test Column E instead of D
' you would have to replace 4 by 5 ...
If (Cells(i, 4).Value - Date) <= 10 And (Cells(i, 4).Value - Date) >= 0 Then
expire = expire & "- " & i & vbCrLf
End If
Next
MsgBox "Items on the following rows are due to expire:" & vbCrLf & vbCrLf & expire & vbCrLf & "Please action.", vbInformation
End Sub

Display More

Hope this will help

**Carim** · Mar 21st 2021

Once you have tested your macro ... feel free to share your comments ...

Help with popup boxes

Participate now!

Share