#1
|
|||
|
|||
Verify Textbox Data is on Array
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 |
#2
|
||||
|
||||
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
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#3
|
|||
|
|||
Thank you very much, it works perfectly. Really appreciate your help
|
Thread Tools | |
Display Modes | |
|
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 |