SelectionChange Event

[Squid]

I am writng some code to validate dates entered. I have some code that
works in combobox_lost focus. It compares a date in a cell H12 to the date
generated from the combobox. But if the user selects the value from the
combobox, prior to entering value in the cell, validation will be skipped.
I want to create some code that when the value in cell H12 changes, my code
is triggered to validate dates. The code should only be triggered when cell
H12 changes.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Workdate As Range
Dim Result as Interger 'Msgbox result
Set Workdate = Range("H12")

If Intersect(Range("H12"), Target) Is Nothing Then
Exit Sub
Else
ValidateDates
End If
End Sub

Private Sub ValidateDate()
Dim Workdate, StartDate, ExpirationDate As Date
Dim Result As Integer

With ActiveSheet
Workdate = .Range("H12").Value
StartDate = .Range("N8").Value
ExpirationDate = DateAdd("yyyy", 1, StartDate) - 1

'Test if workdate enter is within contract effective dates
If Workdate >= StartDate And Workdate <= ExpirationDate Then
Exit Sub
Else
Result = MsgBox("The work date does not fall within the selected
contract period. Are you sure you want to use this contract?", _
vbQuestion + vbOKOnly, "CCF, Inc.")
End If

Thanks

 

[Squid]

Never mind Chip Pearson's website saved the day again.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$H$12" Then
Application.EnableEvents = False
ValidateDate
Else
Exit Sub
End If
Application.EnableEvents = True

End Sub

 

[Squid]

Ok, something really strange after I tested this a few times. All of my
code and events stopped working. When I step through the code, it appears
to work.

 

[Bob Phillips]

Squid,

What probably happened is that the code exited prematurely, either by some
data error or user intervention, thereby not resetting EnabelEvents. I
always add a bit of extra code to trap some errfors and avoid ths problem.
Something like

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Goto ws_exit
If Target.Address = "$H$12" Then
Application.EnableEvents = False
ValidateDate
Else
Exit Sub
End If
ws_exit:
Application.EnableEvents = True

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

You probably disabled events and never reenabled them. Put this in a
general module and run it

Sub TurnOnEvents()
Application.EnableEvents = True
End Sub

you might want to structure your code like this.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error goto ErrHandler
If Target.Address = "$H$12" Then
Application.EnableEvents = False
ValidateDate
End If
ErrHandler:
Application.EnableEvents = True
End Sub

 

[Tushar Mehta]

Somewhere you executed a 'Application.EnableEvents=False' but not the
complementary statement to reenable event interrupts.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions