Verify Textbox Data is on Array

Javi78503 · #1 12-09-2022, 08:48 AM

Hi Everyone.

I am trying to validate the data the user inputs into a textbox field before it is populated onto the spreadsheet. The data in the field is an object number, of which there are 33 correct object numbers. I am trying to make it so if the data the user inputs into the textbox is not one of those numbers it will ask them to please verify and try again. My issue is that once it alerts them that it is incorrect and clears out the box it goes into a msgbox loop. Not sure how to fix it, but I have included my code below. Please bear with me as I don't code often and am pretty basic with my knowledge. Thank you

Code:

Private Sub ObjectCode_AfterUpdate()

Dim ObjectArray As Variant
ObjectArray = Array("6119", 6121, 6129, 6139, 6141, 6142, 6413, 6145, 6146, 6148, 6149, 6223, 6237, 6238, 6239, 6249, 6256, 6259, 6267, 6268, 6269, 6291, 6293, 6294, 6299, 6329, 6339, 6395, 6399, 6410, 6411, 6497, 6499)

Dim ObjectFound As Boolean

Reset_If:
ObjectFound = False

For i = LBound(ObjectArray) To UBound(ObjectArray)
    If ObjectArray(i) = Me.ObjectCode.Value Then
        ObjectFound = True
    End If
Next

If ObjectFound = False Then
    Application.EnableEvents = False
    MsgBox "Not a valid Object Code. Please verify and try again", vbOKOnly, "Invalid Input"
    Me.ObjectCode.Value = Null
    Application.EnableEvents = True
    GoTo Reset_If
End If

End Sub

Guessed · #2 12-10-2022, 05:43 PM

I don't think AfterUpdate is the right trigger. Try using the trigger of leaving the text box instead.

Code:

Private Sub ObjectCode_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  Dim sVals As String, sEntry As String
  sVals = "6119,6121,6129,6139,6141,6142,6413,6145,6146,6148,6149,6223,6237,6238,6239,6249,6256,6259,6267,6268,6269,6291,6293,6294,6299,6329,6339,6395,6399,6410,6411,6497,6499"
  sEntry = Me.ObjectCode.Value
  If Len(sEntry) <> 4 Or Not sVals Like "*" & sEntry & "*" Then
    MsgBox "Not a valid Object Code. Please verify and try again", vbOKOnly, "Invalid Input"
    Me.ObjectCode.Value = Null
    Cancel = True
  End If
End Sub

Javi78503 · #3 12-12-2022, 07:15 AM

Thank you very much, it works perfectly. Really appreciate your help

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
textbox dependent on comboboxes data list	Visor	Word VBA	2	08-05-2019 05:19 AM
Data connection problem excel/sharepoint using an SUMIF array	mwestra74	Excel Programming	10	11-28-2017 06:26 PM
How do I make a checkbox or dropdown item reference data in that specific section of the data array?	dhare	Excel Programming	2	02-24-2016 12:36 PM
Convert String Array to Integer Array from a User Input?	tinfanide	Excel Programming	4	12-26-2012 08:56 PM
How to count the frequency of data and also tally value from an array of Excel record	KIM SOLIS	Excel	5	09-07-2011 09:01 AM