Change Event - Multiple Validation Checks 1

[Airbisk]

Hi,

I have been trying to write a change event procedure that looks at a range of cells and checks on whether the cell = "On Hire Previous Year" or is between a date range (Named Cells FYSD and FYED). If either are TRUE then to move to the next cell and if FALSE display a message box.

I have tried different approaches but cannot seem to get it to work..could you please help with my latest attempt below and advise on some changes that I could make to get it to work..any help as always is appreciated. Thanks in advance.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Rng As Range

Dim Cell As Range

Set Rng = Range("G4:G60")

For Each Cell In Rng

If Cell.Value <> "" And Cell.Value = "On Hire Previous Year" Then

If Cell.Value <> "" And Cell.Value < Sheets("Summary").Range("FYSD").Value Or Cell.Value > Sheets("Summary").Range("FYED").Value Then

MsgBox "Invalid Entry"

End If
End If

Next

End Sub

 

[SkipVought]

Hi Air,

Usually the WorkSheet_Change event operates on the range that changed or the row in which the change occurred. Why would you process the G4:G60 range is no change has occurred in that range?

Some other thoughts on this issue...

if Target.Row <= 3 then Exit Sub   'exit if change occurs in rows 1-3 (heading area)
if Target.Count > 1 the Exit Sub   ' exit if multiple cell paste occurs (this may not be applicable to you)

'only apply this logic when the specified range changes
if Not Intersect(Target, Range("G4:G60")) is Nothing then
  'do your logic here
  'be clear about what are invalid entries. Your current logic is NOT!!!
End if

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

Following the description of the problem the analyse of the cell contents could be:
[tt]Select Case Cell
Case ""
MsgBox "do nothing"
Case "On Hire Previous Year"
MsgBox "OK, text"
Case Sheets("Summary").Range("FYED") To Sheets("Summary").Range("FYSD")
MsgBox "OK, value"
Case Else
MsgBox "Retry"
End Select[/tt]
You cat take an advantage from the fact that excel returns changed cells in the Target range.
You can avoid recursive event calls by setting Application.EnableEvents to False at the beginning and True at the end of event procedure in case of changing cells inside the procedure.

combo

 

[SkipVought]

You might check out somethng like this...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim t As Range, bValid As Boolean
    
    If Target.Row <= 3 Then Exit Sub
    
    For Each t In Target.Cells
        If Not Intersect(t, Range("G4:G60")) Is Nothing Then
        
            bValid = False
            
            Select Case t.Value
                Case "On Hire Previous Year": bValid = True
                Case [FYSD] To [FYED]: bValid = True
            End Select
            
            If Not bValid Then
                t.Select
                MsgBox "Invalid"
            End If
        End If
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Airbisk]

Thank you so much guys. I would love to be able to do this sort of thing, I find it really interesting. Can you recommend any good books that would be a good starting point for learning VBA?

I tried to think logically on how to write my attempt but never thought of utilising the Select Case code and now realise my code doesn't make much sense.

Thanks again for your help.

 

[Airbisk]

Hi Guys,

If you are able to help on this one please. After entering an invalid entry you can move away from the cell and the invalid data remains. I have tried to change the code to clear the cell but it errors, I have tried...

If Not bValid Then
t.Select.ClearContents
MsgBox "Invalid Entry"
End If

If Not bValid Then
t.Select
t.Value = "" MsgBox "Invalid Entry"
End If

 

[SkipVought]

That's because NOTHING is an Invalid Entry!

According to your specification a valid entry is either "On Hire Previous Year" or a date within the specified bounds.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}