Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-09-2022, 08:48 AM
Javi78503 Javi78503 is offline Verify Textbox Data is on Array Windows 10 Verify Textbox Data is on Array Office 2019
Novice
Verify Textbox Data is on Array
 
Join Date: Jul 2021
Posts: 12
Javi78503 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 12-10-2022, 05:43 PM
Guessed's Avatar
Guessed Guessed is offline Verify Textbox Data is on Array Windows 10 Verify Textbox Data is on Array Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #3  
Old 12-12-2022, 07:15 AM
Javi78503 Javi78503 is offline Verify Textbox Data is on Array Windows 10 Verify Textbox Data is on Array Office 2019
Novice
Verify Textbox Data is on Array
 
Join Date: Jul 2021
Posts: 12
Javi78503 is on a distinguished road
Default

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

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Verify Textbox Data is on Array 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
Verify Textbox Data is on Array 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
Verify Textbox Data is on Array 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:54 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft