CODEWITHSUNDEEP
excelvbatextboxhighlight
lowak
lowak

Reputation: 1284

Excel VBA: Highlight textbox value after error occurs

I am trying to highlight entered value in TextBox. TextBox value is representing date value in date forma DD-MM-YYYY. I wrote some code to validate if inserted date is ok (in example 31 of April).

Hightlight itself is not a problem, however I want to do this right after an error occurs. So when I insert 31-04-2014, I should get the message "You have inserted wrong date" and the date value should hightlighted. For now it shows me message, highlights value and focus is set to another CommandButton

So far I made something like this:

Private Sub data_faktury_AfterUpdate()

Dim dzien As Byte, miesiac As Byte
Dim rok As Integer

On Error GoTo blad:

dzien = Mid(data_faktury.Value, 1, 2)
miesiac = Mid(data_faktury.Value, 4, 2)
rok = Right(data_faktury.Value, 4)



Call spr_date(dzien, miesiac, rok)


Exit Sub

blad:
If Err.Number = 13 Then
    If data_faktury <> "" Then
        If Len(data_faktury) < 10 Then: MsgBox ("Źle wpisana data faktury.")
    End If
End If

End Sub

And code for 2nd procedure:

Sub zle()

MsgBox ("Wybrałeś zły dzień")

With Faktura.data_faktury
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.Text)
End With

End Sub

Upvotes: 0

Views: 2596

Answers (1)

Rory
Rory

Reputation: 34055

This is a bit long for a comment so here goes. The basic principle is to use the exit event and cancel when necessary. To prevent this being fired when you close the form, you need to use a flag variable - example userform code:

Private bSkipEvents As Boolean
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If bSkipEvents Then Exit Sub

    With TextBox1
        If Not IsValidDate(.Text) Then
            Cancel = True
            MsgBox "Invalid date"
            .SelStart = 0
            .SelLength = Len(.Text)
        End If
    End With
End Sub

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

Upvotes: 2

Related Questions