Limited Rules for textbox

[nicolewh]

Hi,

I am not that savvy with userforms but I'm catching on.
I have a textbox and I want certain rules to be made for this textbox.
The main rule is that the text box can only include the following numbers/characters/arrangements:

(mm is for two digits for the months of the year. dd is for days of the year. hh hours. mm minutes. The "+" and "-" is actually included in the limited rule)
+mmdd
+mmdd-hh
+mmdd-hhmm
+mmdd6
+mmdd-hh6
+mmdd-hhmm6
(the following number signs "#" represent spaces of numbers (the first or only number sign has a limit of spaces that sheet1:a1 says (i.e. if cell a1 is 4 then it can only have 4 digits)( and the second is the same but the number of spaces is in cell b1 on sheet1)( "*" and "-" included too)
#
#-#
*#
*#-#


I know this would be a lot of work but can someone please give me some tips on how to approach this and do this and that.

I would really appreciate this.

Nicole

 

[pbornemeier]

Allow the user to enter any data into the text box.
Have a submit button that will start a parsing routine.
Parse the textbox.
Highlight any errors.
Popup a message box stating what errors exist.
When the user closes the message box, the text box remains for them to correct

Here is some sample validation code with a few of the branches filled in. You will have to fill in the others by following the ones that I have completed. Note that the date checking is not fully valid because the year is not available. It may be simpler to let the user select the format of the text that they want to use (using option buttons?) then enter a "real" date (day, month, year, hour, min) in another text field which will be validated with IsDate() and appropriately formatted and placed into the text box.

Option Explicit
Function ParseInput(sInput)
    Dim bOK As Boolean
    Dim iAValidLength
    Dim iBValidLength
    Dim bDashPresent As Boolean
    Dim bStarPresent As Boolean
 
    iAValidLength = Sheet1.Range("A1").Value
    iBValidLength = Sheet1.Range("B1").Value
    If InStr(sInput, "-") > 0 Then bDashPresent = True
    If InStr(sInput, "*") > 0 Then bStarPresent = True
 
    bOK = True
    If Left(sInput, 1) = "+" Then
        Select Case Len(sInput)
        Case Is = 5  '+mmdd
            'Fill In
        Case Is = 6  '+mmdd6
            'Fill In
        Case Is = 8  '+mmdd-hh
            'Fill In
        Case Is = 9  '+mmdd-hh6
            'Fill In
        Case Is = 10 '+mmdd-hhmm
            'Fill In
        Case Is = 11 '+mmdd-hhmm6
            If Not ValidateNumber(Mid(sInput, 2, 2), 1, 12) Then bOK = False
            If Not ValidateNumber(Mid(sInput, 4, 2), 1, 31) Then bOK = False
            If Not ValidDaysForMonth(Mid(sInput, 2, 2), Mid(sInput, 4, 2)) Then bOK = False
            If Not ValidateNumber(Mid(sInput, 7, 2), 0, 23) Then bOK = False
            If Not ValidateNumber(Mid(sInput, 9, 2), 1, 60) Then bOK = False
            If Mid(sInput, 6, 1) <> "-" Then bOK = False
            If Right(sInput, 1) <> "6" Then bOK = False
        Case Else
            'Invalid Input
            bOK = False
        End Select
    Else
        If Left(sInput, 1) = "*" Then
            If InStr(sInput, "-") > 0 Then  '*#-#
                If Not ValidateNumber(Mid(sInput, 2, iAValidLength), 0, 10 ^ iAValidLength - 1) Then bOK = False
                If Mid(sInput, iAValidLength + 2, 1) <> "-" Then bOK = False
                If Not ValidateNumber(Mid(sInput, iAValidLength + 3, iBValidLength), 0, 10 ^ iBValidLength - 1) Then bOK = False
            Else                            '*#
            'Fill In
            End If
        Else
            If InStr(sInput, "-") > 0 Then  '#-#
            'Fill In
            Else                            '#
            'Fill In
            End If
        End If
 
    End If
    ParseInput = bOK
 
End Function
 
Function ValidateNumber(sInput As String, lMin As Long, lMax As Long)
    'Ensures a character string is composed of digits and is between or equal to either lMin and lMax
    Dim iX As Integer
    Dim iTally As Integer
 
    ValidateNumber = False
    For iX = 1 To Len(sInput)
        iTally = iTally + Not IsNumeric(Mid(sInput, iX, 1))
    Next
    If iTally = 0 Then '0 if all characters were digits
        If IsNumeric(Left(sInput, 1)) Then
            If IsNumeric(Right(sInput, 1)) Then
                If Val(sInput) >= lMin And Val(sInput) <= lMax Then
                    ValidateNumber = True
                End If
            End If
        End If
    End If
End Function
Function ValidDaysForMonth(iMonth As Integer, iDay As Integer)
    'for a given month and day combination return True if OK
    'Note Feb 29 will be OK for all years, not just leap years
    ValidDaysForMonth = False
    Select Case iMonth
    Case Is = 9, 4, 5, 11
        If iDay >= 1 And iDay <= 30 Then ValidDaysForMonth = True
    Case Is = 2
        If iDay >= 1 And iDay <= 29 Then ValidDaysForMonth = True
    Case Else
        If iDay >= 1 And iDay <= 31 Then ValidDaysForMonth = True
    End Select
End Function

 

[nicolewh]

Thank you so much! I have been working on this for the last several days and have made little progress. So thank you.

Nicole