Checking For Expired Dates In A Range

stickyfeet · Dec 7th 2006

Hi

I currently have some code which checks the dates in a range when the workbook first opens.

If the date in the range matches the current date or is older than the current date then I'd like a Userform to popup warning the user that something has expired.

I'd like to add sometimes that some of the cells in the range may be blank.

Currently the Userform pops up regardless

I hope someone can help

Here's what I currently have ..... Thanks

Code

Private Sub Workbook_Open()
                                     
                      
'Check Expiry Dates


Dim ACell As Range


For Each ACell In Sheets("Information").Range("i7:i100")
        
        If ACell.Value = Date Then
        UserForm5.Show
        GoTo AA
                
        ElseIf ACell.Value < Date Then
        UserForm5.Show
        GoTo AA
        
        Else
        
        End If
  Next ACell


AA:




End Sub

Display More

GeorgS · Dec 8th 2006

Re: Checking For Expired Dates In A Range

stickyfeet,

Try converting the cell value to a date:

Code

If CDate(ACell.Value) = Date Then

If you get an error doing this, then you may need to reformat it in to something that looks like a date.

G.

CharlesH · Dec 8th 2006

Re: Checking For Expired Dates In A Range

stickyfeet,

Try this. If a empty cell is encountered it will not product the userform.

Code

Private Sub Workbook_Open()
     
     
     'Check Expiry Dates
     
    Dim ACell As Range
     
    For Each ACell In Sheets("Sheet1").Range("i7:i100")
         
        If ACell.Value = Date And ACell.Value <> "" Then
            UserForm5.Show
            GoTo AA
             
        ElseIf ACell.Value < Date And ACell.Value <> "" Then
            UserForm5.Show
            GoTo AA
             
        Else
             
        End If
    Next ACell
     
AA:
     
     
End Sub

Display More

stickyfeet · Dec 8th 2006

Re: Checking For Expired Dates In A Range

Thanks Charles,

I just changed it to the following before I had a chance to read your post. Which also seems to work.

I'll try your way and stick with that (as you can tell by my code, I'm not a coder)

Thanks for your help.

Regards

Code

Dim ACell As Range


For Each ACell In Sheets("Information").Range("i7:i100")


        
        If ACell.Value > Date Then
        GoTo AA
                    
        ElseIf ACell.Value = "" Then
        GoTo AA
        
        Else
        
        UserForm5.Show
        GoTo BA
          
        End If
        
AA:
        
  Next ACell


BA:


End Sub

Display More

Dave Hawley · Dec 8th 2006

Re: Checking For Expired Dates In A Range

I think you want this

Code

Private Sub Workbook_Open()
     
     'Check Expiry Dates
     
    Dim ACell As Range
    Dim bMatch As Boolean
     
    For Each ACell In Sheets("Information").Range("i7:i100")
      If IsDate(ACell) Then
        bMatch = ACell.Value <= Date
        If bMatch = True Then Exit Sub
      End If
    Next ACell
      
     If bMatch = True Then UserForm5.Show
End Sub

Display More

stickyfeet · Dec 8th 2006

Re: Checking For Expired Dates In A Range

Wow, thanks Dave

Checking For Expired Dates In A Range

Participate now!

Share