CODEWITHSUNDEEP
vba
FaruSZ
FaruSZ

Reputation: 61

specify textbox value vba

I have a textbox in a userform that help the user to add date to my databse, the user must enter a code in this format: "2019-254" ("a year - 3 Numbers"). If teh format is not correct an error msg appears to tell the user to repeat the operation using the correct format. I know how to do this when working with dates like the foloowing code:

Private Sub Valider_Click()
If Not IsDate(TextBox_Date) Then
    MsgBox "Please enter a correct date:10/09/2019", vbCritical
Else
ws.Cells(24, Fin_Col_IT + 1) = CDate(Date_IT)
end if 
end sub

is it possibile to make a condition when not working with dates ?

Upvotes: 0

Views: 40

Answers (2)

FaneDuru
FaneDuru

Reputation: 42236

Use the next function, please. Do you also need to check if the first four digits are a year significance? If yes, the function must be adapted, but you must explain the logic for the valid range...

Private Function Valider_Nubmers(strNumb As String) As Boolean
  If strNumb Like "####-###" Then Valider_Nubmers = True
End Function

It can be called in this way:

Sub testValider_Nubmers()
  Dim x As String
  x = "2019-254"
  'x = "234-000"
  If Valider_Nubmers(x) Then
    MsgBox "Correct"
  Else
    MsgBox "Incorrect"
  End If
End Sub

Upvotes: 1

FunThomas
FunThomas

Reputation: 29146

If you simply want to check if the data entered follows a pattern, you can use the like operator

If Not s Like "####-###" Then 
    MsgBox ...
End If

(s is the string you are checking)

If you also want to check if year is valid (let's say between 2000 and the current year) or the number has a specific value (eg <= 500) , you have to do the work manually using string functions. The following function can give you an idea how this could work:

Function checkInput(s As String) As Boolean
    checkInput = False
    If Not s Like "####-###" Then Exit Function              ' wrong pattern
    
    Dim tokens() As String, yyyy As Long, nnn As Long
    tokens = Split(s, "-")
    
    yyyy = Val(tokens(0))
    nnn = Val(tokens(1))
    
    If yyyy < 2000 Or yyyy > year(Now) Then Exit Function   ' wrong year
    If nnn > 500 Then Exit Function                         ' wrong number
    
    checkInput = True    ' Okay
End Function

Upvotes: 2

Related Questions