Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SelectionChange Event
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SelectionChange Event
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" wrote in message news:8iiWb.2437$yE5.13085@attbi_s54... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code stopped working??!
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. "Squid" wrote in message news:8iiWb.2437$yE5.13085@attbi_s54... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code stopped working??!
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) "Squid" wrote in message news:cfjWb.2965$_44.6780@attbi_s52... 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. "Squid" wrote in message news:8iiWb.2437$yE5.13085@attbi_s54... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code stopped working??!
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 -- Regards, Tom Ogilvy "Squid" wrote in message news:cfjWb.2965$_44.6780@attbi_s52... 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. "Squid" wrote in message news:8iiWb.2437$yE5.13085@attbi_s54... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to use selectionchange instead of doubleclick event? | Excel Discussion (Misc queries) | |||
Worksheet SelectionChange Event | Excel Discussion (Misc queries) | |||
Disable SelectionChange Event | Excel Discussion (Misc queries) | |||
Excel Automation SelectionChange event | Excel Discussion (Misc queries) | |||
selectionchange problem | Excel Programming |