Check Expiry dates from multiple sheets with a pop up box

[Kjjb]

Hi ,
Surely there must be a thread for my query , but cannot seem to find it .Appreciate your help.
I have a worksheet with 4 sheets named "D1D","D2D","D3D","Misc" . I need to write a VBA to check all the 4 sheets for expiry date ( today()-30) in column E ( range E 7:E135) and then pop message should state Certificates have expired. The pop up box/check should be done when I open the workbook.
Appreciate your help.

Thanks in advance

 

[Legacy 143009]

I have just realised it works only for sheet D1D .. then give a run time error. I have attached an image

You don't have a sheet named as D2D.

 

[Kjjb]

You don't have a sheet named as D2D.

Hi <

I have attached a screen shot pointing to the sheet named D2D( hope its correct) ....also the VBA works with sheets D1D,D2D & D3D ..but returns a run time error with sheets Misc & various.
Please assist
Thanks

 

[Kjjb]

Hi <

I have attached a screen shot pointing to the sheet named D2D( hope its correct) ....also the VBA works with sheets D1D,D2D & D3D ..but returns a run time error with sheets Misc & various.
Please assist
Thanks

 

[Legacy 143009]

I have a worksheet with 4 sheets named "D1D","D2D","D3D","Misc" .

It is because you have extra space in sheet name "Misc ". You should also add the name "Various" into the list if you want. You haven't mentioned in the first post.

Private Sub Workbook_Open()
  Dim wsNames() As string
  wsNames = Split("D1D,D2D,D3D,Misc ,Various", ",") 'You must add the sheet names you want into this list.
  For i = 0 To 3
    With Worksheets(wsNames(i))
      For j = 7 to 135
        If .Cells(j, 5).Value > Now()-30 Then
          MsgBox "Sheet " & wsNames(i) & " Date E" & j & " has expired."
        End If
      Next
    End With
  Next
End Sub

 

[Kjjb]

Guys Thanks a LOT !
All sorted out ..the sheet names had a space , hence it an error.

THANK YOU VERY MUCH FOR ALL THE HELP

 

[Kjjb]

It is because you have extra space in sheet name "Misc ". You should also add the name "Various" into the list if you want. You haven't mentioned in the first post.

Private Sub Workbook_Open()
  Dim wsNames() As string
  wsNames = Split("D1D,D2D,D3D,Misc ,Various", ",") 'You must add the sheet names you want into this list.
  For i = 0 To 3
    With Worksheets(wsNames(i))
      For j = 7 to 135
        If .Cells(j, 5).Value > Now()-30 Then
          MsgBox "Sheet " & wsNames(i) & " Date E" & j & " has expired."
        End If
      Next
    End With
  Next
End Sub

Yes you right .. THANK YOU ..MOST HELPFUL

 

[Kjjb]

Hi Flashbond,
Sorry about this, the VBA you sent me worked perfectly. However I have inserted a few words in the rows E 38 & 72 for column E for the expiry date .hence the msg box keeps stating the dates have expired. Could you help to write a VBA to run a check on column E for the expiry dates but excluding cells E 38 & E 72.
I have attached the screen shot .
Thanks A LOT !

 

[Legacy 143009]

How about this?

Private Sub Workbook_Open()
  Dim wsNames() As string
  wsNames = Split("D1D,D2D,D3D,Misc ,Various", ",") 'You must add the sheet names you want into this list.
  For i = 0 To 3
    With Worksheets(wsNames(i))
      For j = 7 to 135
        If IsDate(.Cells(j, 5).Value) Then
          If .Cells(j, 5).Value > Now()-30 Then
            MsgBox "Sheet " & wsNames(i) & " Date E" & j & " has expired."
          End If
        End If
      Next
    End With
  Next
End Sub

 

[Kjjb]

Perfect .. You a genius ! Thanks a lot

VERY MUCH APPRECIATED

 

[Legacy 143009]

No problem