VBA Userform Help Required - Stuck

[ddoublev]

Hi,

Is there a way to call the sub attached to a datepicker_change() each time the value is changed after userform initialization?

Below is the event code, what i have found is that if the user selects the wrong date initally and an existing record is found which returns values into the textbox controls, after they change the date again to the correct date the change event does not get called again and the form remains in the "update" state.

[COLOR=#333333]
Private Sub BoxDate_Change()
    
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
    
Dim dFind As Date
Dim ctl As MSForms.Control


If IsDate(BoxDate) = False Then Exit Sub


dFind = BoxDate




With Range("DateWS") 'a named range on sheet listing dates
Set DateFound = .Find(dFind)
If DateFound Is Nothing Then
Exit Sub
Else
With DateFound
On Error Resume Next


If .Offset(0, 1) <> "" Then
BoxOperator = .Offset(0, 1)
Else: BoxOperator = ""
End If
If .Offset(0, 2) <> "" Then
Page1Box1 = .Offset(0, 2)
Else: Page1Box1 = ""
End If
If .Offset(0, 3) <> "" Then
Page1Box2 = .Offset(0, 3)
Else: Page1Box2 = ""
End If
If .Offset(0, 4) <> "" Then
Page1Box3 = .Offset(0, 4)
Else: Page1Box3 = ""
End If
End With
End If
End With
Application.DisplayStatusBar = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
cmdSubmit.Caption = "Update & Close"


MsgBox ("Record Found!")


End Sub[/COLOR]

Any ideas how I can have the event triggered each time the date picker value is changed? Or does the userform need to be reset? How can this be done?

Cheers

Dylan

 

[ddoublev]

Anyone? Please?

 

[V_Malkoti]

Are you sure that your BoxDate_Change() event code gets completely executed the first time? Since you have Application.EnableEvents = True statement at the end of the subroutine, it is possible that it doesn't get executed because of one of the Exit Sub statements.

 

[ddoublev]

Hi V_Malkoti
What would you suggest be done to ensure the sub is completely executed?

 

[V_Malkoti]

Create a line label for all of the reset statements at the end and instead of using exit sub, jump to that line label. I've made some changes to your code and highlighted text in blue to show those changes.

Private Sub BoxDate_Change()
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    
[COLOR=#0000FF]    [/COLOR]' if any error occurs, execute the handler block before exiting
[COLOR=#0000ff]    On Error GoTo ExitHandler[/COLOR]
    
    Dim dFind As Date
    Dim ctl As MSForms.Control


    If IsDate(BoxDate) = False Then [COLOR=#0000ff]GoTo ExitHandler[/COLOR]


    dFind = BoxDate


    With Range("DateWS") 'a named range on sheet listing dates
        Set DateFound = .Find(dFind)
        If DateFound Is Nothing Then
            GoTo ExitHandler
        Else
            With DateFound
                On Error Resume Next
                If .Offset(0, 1) <> "" Then
                    BoxOperator = .Offset(0, 1)
                Else: BoxOperator = ""
                End If
                If .Offset(0, 2) <> "" Then
                    Page1Box1 = .Offset(0, 2)
                Else: Page1Box1 = ""
                End If
                If .Offset(0, 3) <> "" Then
                    Page1Box2 = .Offset(0, 3)
                Else: Page1Box2 = ""
                End If
                If .Offset(0, 4) <> "" Then
                    Page1Box3 = .Offset(0, 4)
                Else: Page1Box3 = ""
                End If
                ' reset error handling to excel default - showing the error message
[COLOR=#0000ff]                On Error GoTo 0[/COLOR]
            End With
[COLOR=#0000ff]            ' show message box if record was found[/COLOR]
[COLOR=#0000ff]            MsgBox ("Record Found!")[/COLOR]
[COLOR=#0000ff]            ' and update button text[/COLOR]
[COLOR=#0000ff]            cmdSubmit.Caption = "Update & Close"[/COLOR]
        End If
    End With
    
    
[COLOR=#0000ff]ExitHandler:[/COLOR]
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub