none
Min & Max Arrays RRS feed

  • Question

  • Hi Everyone,

    I have a UserForm with ListBox1, TextBox1, TextBox2

    In ListBox1 I have the following items - Automobiles, Building, Software

    TextBox1 auto populates based on what the user selects from ListBox1 ( For example, If I click Automobiles TextBox1 will say "3-5 years")

    TextBox 2 is a manual input from a user. When the user inputs a number outside the range from TextBox1, an error message pops up, that is working perfectly...EXCEPT what happens when TextBox1 populates with a single text number rather than a range. For An example, If User Clicks on Buildings, TextBox1 will now say "20 years" opposed previously when user clicked on Automobiles it said "3-5 years". My code Error reads Subscript Out Of Range. I know its an error since Min is at 0 and Max is at 1, But I'm not sure how to fix this.

    Thank you for help, below is the code for my UserForm as well as TextBox2.

    UserForm Code

    Function IsValidEntry(ByVal Entry As Object, ByVal Period As String) As Boolean
        Dim s() As String
        Dim Min As Integer, Max As Integer
    'extract Min Max values
        s = Split(Period, "-")
        s = Split(Period, "years")
       
    'coerce string to number
        Min = Val(s(0)): Max = Val(s(1))
    'check entry valid
        IsValidEntry = CBool(IsNumeric(Entry.Text) And Val(Entry.Text) >= Min And Val(Entry.Text) <= Max)
    'inform user
        If Not IsValidEntry Then
            MsgBox "Invalid Entry" & Chr(10) & "Enter A Numeric Value In Range Of" & Chr(10) & _
                                                "Min: " & Min & " Max: " & Max, 16, "Invalid Entry"
        End If
    End Function

    TextBox2 Code:

    Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
     If Not IsValidEntry(Me.TextBox2, Me.TextBox1.Text) Then
     End If
    End Sub

    Tuesday, March 27, 2018 10:08 PM

All replies

  • Hello rami17,

    In my testing, your shared code does not work for me. s=Split(Period,"-") does not make sense since s will be reset via Split(Period, "years").

    In this situation, used "3-5 years" for testing, S(0) is "3-5" and S(1) is "". I don't think this will work as your want. Did you change part of the code while sharing it?

    I would also suggest you check if below code could work for you.

    Function IsValidEntry(ByVal Entry As Object, ByVal Period As String) As Boolean
        Dim s() As String
        Dim Min As Integer, Max As Integer
        'get the number string of the value, such as 3-5, 20, 1-2,
        numberString = Trim(Split(Period, "years")(0))
        'try to use "-" to split the numberstring
        s = Split(numberString, "-")
        
    'if the result array contains only one value, that means the numberstring is a single nmuber, such as 20, 1, 2 etc
    'for this situation, set the min and max using the same singe number
    If (UBound(s) - LBound(s)) = 0 Then
        Min = Val(s(0)): Max = Val(s(0))
    Else
        Min = Val(s(0)): Max = Val(s(1))
    End If
    'check entry valid
        IsValidEntry = CBool(IsNumeric(Entry.Text) And Val(Entry.Text) >= Min And Val(Entry.Text) <= Max)
    'inform user
        If Not IsValidEntry Then
            MsgBox "Invalid Entry" & Chr(10) & "Enter A Numeric Value In Range Of" & Chr(10) & _
                                                "Min: " & Min & " Max: " & Max, 16, "Invalid Entry"
        End If
    End Function

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 28, 2018 1:49 AM
  • Hello,

    Has your original issue been resolved? If it has, I would suggest you mark the helpful reply as answer or provide your solution and mark as answer to close this thread. If not, please feel free to let us know your current issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 30, 2018 8:45 AM