# Check Expiry dates from multiple sheets with a pop up box



## Kjjb (Dec 26, 2022)

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


----------



## Flashbond (Dec 26, 2022)

In the VBA project explorer window double click to ThisWorkbook on the left pane and paste the code.

```
Private Sub Workbook_Open()
  Dim wsNames() As string
  wsNames = Split("D1D,D2D,D3D,Misc", ",")
  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 (Dec 26, 2022)

Hi Flashbond,
THANK YOU for the prompt reply.

However i am getting a runtime error 9 , script out of range 
With Worksheets(wsNames(i))  
Please assist
Thanks


----------



## Flashbond (Dec 26, 2022)

This error is caused if you don't have sheets named as "D1D","D2D","D3D" or "Misc".


----------



## shinigamilight (Dec 26, 2022)

```
Sub works()
      Dim names
      Dim k As Integer
      names = Array("D1D", "D2D", "D3D", "Misc")
      Dim i As Long
      For Each names In Worksheets
            For i = 7 To 135
                If names.Range("E" & i) = Date - 30 Then
                    MsgBox "Sheet name: " & names.Name & vbNewLine & names.Range("E" & i).Address & " Certificates have expired"
                End If
            Next i
     Next names
End Sub
```


----------



## Kjjb (Dec 26, 2022)

Flashbond said:


> This error is caused if you don't have sheets named as "D1D","D2D","D3D" or "Misc"



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


----------



## Kjjb (Dec 26, 2022)

shinigamilight said:


> ```
> Sub works()
> Dim names
> Dim k As Integer
> ...





shinigamilight said:


> ```
> Sub works()
> Dim names
> Dim k As Integer
> ...


Hi ,

Tried the code ..nothing happens.


----------



## shinigamilight (Dec 26, 2022)

Kjjb said:


> Hi ,
> 
> Tried the code ..nothing happens.


Are you getting any errors or something?


----------



## Kjjb (Dec 26, 2022)

Kjjb said:


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


Hi , 

Did a few adjustment to the sheet and now only sheets Misc & various  do not work. Getting a run time error ??
Please advise


----------



## Kjjb (Dec 26, 2022)

shinigamilight said:


> Are you getting any errors or something?


no errors ..just nothing happens


----------



## Kjjb (Dec 26, 2022)

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


----------



## Flashbond (Dec 26, 2022)

Kjjb said:


> 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 (Dec 26, 2022)

Flashbond said:


> 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 (Dec 26, 2022)

Kjjb said:


> 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


----------



## Flashbond (Dec 26, 2022)

Kjjb said:


> 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 (Dec 26, 2022)

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 (Dec 26, 2022)

Flashbond said:


> 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()
> ...


Yes you right .. THANK YOU ..MOST HELPFUL


----------



## Kjjb (Dec 28, 2022)

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 !


----------



## Flashbond (Dec 28, 2022)

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 (Dec 28, 2022)

Perfect .. You a genius ! Thanks a lot 

VERY MUCH APPRECIATED


----------



## Flashbond (Dec 28, 2022)

No problem


----------



## Kjjb (Dec 26, 2022)

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


----------



## Flashbond (Dec 28, 2022)

Small revision: 
	
	
	
	
	
	



```
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 UBound(wsNames)
    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 (Dec 28, 2022)

Flashbond said:


> Small revision:
> 
> 
> 
> ...


EXCELLENT ..works perfectly.. THANK YOU VERY MUCH


----------

