Exiting a Textbox Problem

[Pacman52]

Hi all, So after hours of trying to workout myself why I can't get this to work I've finally given up trying and would be really grateful if someone could tell me what I'm doing wrong please.

So in the code below I'm trying to exit the textbox 'textboxInvoiceDate' and show the msgbox asking has the invoice been paid. If the response is Yes then I need to be able to set the focus to the 'textboxDateInvoicePaid' if the response is no I want to disable the 'textboxDateInvoicePaid' and textboxPaymentMethod and move the focus to the next relevant box (textboxInvoiceDetails).

The problem I'm getting is the msgbox asking if the invoice has been paid appears as expected but the focus stays in the textbox and the msgbox appears again I then tab out of the textbox again and the msgbox again shows - its like the textbox isn't exiting initially and re-setting the focus back to itself.

Could anyone advise what I'm missing here or doing wrong.

Many thanks Paul

Private Sub textboxInvoiceDate_Enter()

    textboxInvoiceDate.BackColor = RGB(204, 255, 255)

End Sub

Private Sub textboxInvoiceDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

    If Not IsDate(textboxInvoiceDate) Then
        MsgBox "Your entry is not recognised as a date. Please only enter a date in the correct format e.g. 'DD/MM/YYYY'", vbExclamation, "Invalid Entry"
        Cancel = True
        textboxInvoiceDate = ""
        textboxInvoiceDate.BackColor = RGB(204, 255, 255)
        textboxInvoiceDate.SetFocus

    Else

        textboxInvoiceDate = Format(CDate(textboxInvoiceDate), "DD/MM/YYYY")

    End If

End Sub

Private Sub textboxInvoiceDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    Dim MsgReply1 As VbMsgBoxResult
    
    If textboxInvoiceDate.Value = "" Then

        Cancel = True
        MsgBox "You must enter the date of the invoice.", vbExclamation, "Date Required"
        textboxInvoiceDate.SetFocus
        textboxInvoiceDate.BackColor = RGB(204, 255, 255)
        
    Else
        
    If textboxInvoiceDate.Value >= "" Then
    
        MsgReply1 = MsgBox("Has this invoicealready been paid?", vbQuestion + vbYesNo, "Payment Method")
        
        If MsgReply1 = vbYes Then
    
        textboxDateInvoicePaid.Enabled = True
        textboxDateInvoicePaid.SetFocus
        textboxPaymentMethod.Enabled = True
        textboxInvoiceDate = Format(CDate(textboxInvoiceDate), "DD/MM/YYYY")
        textboxInvoiceDate.BackColor = RGB(255, 255, 255)

Else 
        textboxDateInvoicePaid.Enabled = False
        textboxPaymentMethod.Enabled = False
        textboxInvoiceDetails.SetFocus
     
    End If
    End If
        End If

End Sub

 

[bobsan42]

disable application events at the beginning of the exit procedure and when done enable them again, for example:

sub textbox1_exit ()
Application.EnableEvents = False
....
...
....
Application.EnableEvents = true
end sub

the Exit Event fires every time you try to exit the textbox, even when you do it by code.

 

[Pacman52]

Thanks so much for the reply,

Sadly adding the application events doesn't seem to do the trick either although the textboxDateInvoicePaid does now change its backcolour, the textboxInvoiceDate that I'm trying to exit from now highlights the date value in the box and keeps the focus until I then tab out of it again. Very odd.

 

[dmt32]

Thanks so much for the reply,

Sadly adding the application events doesn't seem to do the trick

That event does not apply to userform events.

Dave

 

[Akuini]

@Pacman52
What happen if you use "textboxInvoiceDate_AfterUpdate" instead of "textboxInvoiceDate_Exit"?

 

[dmt32]

Hi,

Sorry, did not have much time yesterday - this is only lightly tested but see if this update to your code will do what you want

1 – make back-up of your workbook

2 – delete both existing textboxInvoiceDate_Exit & textboxInvoiceDate_BeforeUpdate event codes.

Add these codes to your Userform

Dim EventsOff As Boolean
Private Sub textboxInvoiceDate_Enter()
    Me.textboxInvoiceDate.BackColor = RGB(204, 255, 255)
End Sub

Private Sub textboxInvoiceDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim InvoicePaid As Boolean
    Dim InvoiceDate As Variant
   
    If EventsOff Then Exit Sub
   
    InvoiceDate = Me.textboxInvoiceDate.Value
   
    Cancel = Not IsDate(InvoiceDate)
   
    If Cancel Then
   
        MsgBox "Please enter a valid date in the correct format e.g. 'DD/MM/YYYY'", vbExclamation, "Invalid Entry"
      
    Else

        Me.textboxInvoiceDate.Value = Format(CDate(InvoiceDate), "DD/MM/YYYY")
          
        InvoicePaid = MsgBox("Has this invoice already been paid?", 36, "Payment Method") = vbYes
       
        EventsOff = True
       
        With textboxDateInvoicePaid
            .Enabled = InvoicePaid
            If .Enabled Then .SetFocus: Me.textboxInvoiceDate.BackColor = rgbWhite
         End With
        
         With Me.textboxPaymentMethod
            .Enabled = InvoicePaid
        If Not .Enabled Then Me.textboxInvoiceDetails.SetFocus
        End With

    End If
   
    EventsOff = False
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    EventsOff = True
End Sub

Note the variable EventsOff at the top of the codes – this MUST be placed at the very TOP of your userforms code page OUTSIDE any procedure.

Hopefully, update will do what you want but just to make you aware that when posting Dates from a textbox to a range that these are strings.
To convert to date you should use one of the available type conversion functions to coerce to date & then apply the required display format using Range.NumberFormat.

e.g.

   With Range("A1")
        .Value = CDate(Me.textboxInvoiceDate.Value)
        .NumberFormat = "DD/MM/YYYY"
    End With

Hope Helpful

Dave

 

[Pacman52]

That event does not apply to userform events.

Dave

that would be the reason it doesn't work then - thanks Dave

 

[Pacman52]

Hi,

Sorry, did not have much time yesterday - this is only lightly tested but see if this update to your code will do what you want

1 – make back-up of your workbook

2 – delete both existing textboxInvoiceDate_Exit & textboxInvoiceDate_BeforeUpdate event codes.

Add these codes to your Userform

Dim EventsOff As Boolean
Private Sub textboxInvoiceDate_Enter()
    Me.textboxInvoiceDate.BackColor = RGB(204, 255, 255)
End Sub

Private Sub textboxInvoiceDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim InvoicePaid As Boolean
    Dim InvoiceDate As Variant
  
    If EventsOff Then Exit Sub
  
    InvoiceDate = Me.textboxInvoiceDate.Value
  
    Cancel = Not IsDate(InvoiceDate)
  
    If Cancel Then
  
        MsgBox "Please enter a valid date in the correct format e.g. 'DD/MM/YYYY'", vbExclamation, "Invalid Entry"
     
    Else

        Me.textboxInvoiceDate.Value = Format(CDate(InvoiceDate), "DD/MM/YYYY")
         
        InvoicePaid = MsgBox("Has this invoice already been paid?", 36, "Payment Method") = vbYes
      
        EventsOff = True
      
        With textboxDateInvoicePaid
            .Enabled = InvoicePaid
            If .Enabled Then .SetFocus: Me.textboxInvoiceDate.BackColor = rgbWhite
         End With
       
         With Me.textboxPaymentMethod
            .Enabled = InvoicePaid
        If Not .Enabled Then Me.textboxInvoiceDetails.SetFocus
        End With

    End If
  
    EventsOff = False
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    EventsOff = True
End Sub

Note the variable EventsOff at the top of the codes – this MUST be placed at the very TOP of your userforms code page OUTSIDE any procedure.

Hopefully, update will do what you want but just to make you aware that when posting Dates from a textbox to a range that these are strings.
To convert to date you should use one of the available type conversion functions to coerce to date & then apply the required display format using Range.NumberFormat.

e.g.

   With Range("A1")
        .Value = CDate(Me.textboxInvoiceDate.Value)
        .NumberFormat = "DD/MM/YYYY"
    End With

Hope Helpful

Dave

Hi Dave - Sadly that didn't work either - same issue as before that the focus returned to the textbox. Event though my original code isn't perfect from a pro's point of view, I couldn't understand why it wouldn't move to the next textbox so as a test I've deleted the actually textbox and put a new one in and it now works fine with my original code. So it appears there must of been some sort of 'corruption' on the box itself possibly.

Also thanks for adding the bit about the date conversion - I remembered that from one of your previous responses to one of my questions and had already added it when writing the data to the ws.

Thanks again and thanks to all those who replied Paul

 

[Akuini]

same issue as before that the focus returned to the textbox.

Did you try my suggestion in post #5?

 

[dmt32]

Hi Dave - Sadly that didn't work either

Curious seemed to work ok on my little test file but as say, if there was an issue with one of your textboxes then likely to have been the problem.

Anyway, glad you have a resolution & appreciate feedback

Dave