0

I am creating a userform with approximately 75 text boxes. I would like to create a common function to test the validity of the data for all text boxes by passing the allowable characters to the function along with the string I wish to validate.

  • For some boxes, I want to check only for numerics (like a phone number with (), -, and spaces stripped prior to calling the function).
  • For boxes such as last name, I want to test for upper and lower case alpha as well as allowing embedded spaces and a hyphen.
  • For other boxes that allow other free form text, I want to restrict input to selected character such as letters, numbers, hyphens, left and right parens "()", etc.

I cannot get the syntax right so that the "Is...Like" works correctly. I get "Run time error 5"..."Invalid procedure call or argument".

Here is my function:

Function IsValidString(strValue As String, strAllowed As String) As Boolean
    Dim intPos As Integer
    Dim strTemp As String
    strTemp = """" & strAllowed & """"
    MsgBox "StrValue = " & strValue & vbCrLf & vbCrLf & "StrAllowed = " & strAllowed & vbCrLf & vbCrLf & "strTemp = " & strTemp

    For intPos = 1 To Len(strValue)
        If Not (Mid(strValue, i, 1) Like strAllowed) Then IsValidString = False
    Next intPos
    IsValidString = True
End Function

To call the function, I use:

If Not IsValidString(strTemp, "[A-Za-z0-9]/#-") Then MsgBox "You Lose"

The error occurs within the Function code. It does not matter if I test for strAllowed or strTemp; I get the same error.

Thanks for looking at it.

PaichengWu
  • 2,649
  • 1
  • 14
  • 28
Papa
  • 5
  • 5
  • 2
    Many issues: 1) you have a typo - `Mid(strValue, i, 1)` should be `Mid(strValue, intPos, 1)` 2) you run a For loop, possibly setting `IsValidString` to False, then set it back to True. 3) your Function compares individual characters to a pattern. The example passes a pattern that will only match a multiple character string. – chris neilsen Aug 07 '18 at 02:02
  • 2
    Put `Option Explicit` at the top of your module, then fix the compile error. In this case, `i` is not declared. I'm guessing it should be `intPos`. – Comintern Aug 07 '18 at 02:02
  • 1
    You can restrict what user can type in with the `TextBox1_KeyDown` event instead or large amount of checks afterwards. – PatricK Aug 07 '18 at 02:08
  • I am getting tired. I cannot believe that I missed substituting "intPos" for i. That was the this issue. Thanks for solving what turned out to be an extremely minor issue. – Papa Aug 07 '18 at 02:12
  • @Papa to be clear, that is indeed a very minor issue. But your code has extremely major problems, the first of which is dealing with 75 `MSForms.TextBox` controls. Unless you're generating these controls dynamically, you're going to have a ridiculous amount of copy-pasta code in that form's code-behind, and a guaranteed complete wreck of a maintenance nightmare. Extracting that validation function was a very, very good idea though. – Mathieu Guindon Aug 07 '18 at 02:15
  • Not sure else else to handle it. This is a user form that is gathering information from a group of volunteers who serve in a prison ministry. We have a boatload of forms to fill out to satisfy the OH Dept of Rehabilitation and Corrections. Many questions are option boxes... have you ever done this or that and if you respond Yes, then we capture the response. The intent is to create a spreadsheet with the answers, take the answers, and overlay multiple PDFs with the responses. This will replace a relational DB as the guy who has the DB will retire with no future access to the DB software. – Papa Aug 07 '18 at 02:22
  • I am gathering personal info (name, address, city/ST/ZIP, phones, etc). Then I need to ask specific questions for each required state ODRC form. It's a pain but I need each of the 50-75 text boxes to fill out each form. And, yes, you are right that there is a lot of copy/paste of code. That is why I am trying to reduce the number of "Is this valid" functions down to one based upon the question that is being asked. – Papa Aug 07 '18 at 02:24
  • Have you considered [using regular expressions](https://stackoverflow.com/q/22542834/4088852) instead? That would be much simpler and much more performant. You could put the expression in the `TextBox.Tag`. – Comintern Aug 07 '18 at 02:26
  • I've searched this site a boatload of time but have only flagged one previous response as asked and answered. How to do I flag the above responses as answering my question? – Papa Aug 07 '18 at 02:28
  • 1
    Just out of curiosity, why are you replacing the relational DB? If you just need a user interface, you can push the form results to the database with ADO. – Comintern Aug 07 '18 at 02:29
  • 1
    You can't. If the comments provided an answer to your question and you feel it would be valuable to others, just "self answer" and accept that. In this case, I'd probably close it as a typo. – Comintern Aug 07 '18 at 02:30
  • We do not host this on a public server; it is private and as such, when he retires we lose access to the HW and SW. Since we are a volunteer organization, we don't have the $ to purchase space on a public server. – Papa Aug 07 '18 at 02:33
  • @Comintern... I am a retired mainframe sysprog. To get my occasional coding fix, I dabble in VBA, html, Javascript, etc. as a volunteer to support various groups with my limited expertise. I have maintained a website for another group for the past 15 years and have recently been asked to take over this task of maintaining an Excel WB to capture this information. It keeps me off the streets and gives me something to do after my wife goes to bed. – Papa Aug 07 '18 at 02:39
  • 1
    You could get SQL Server Express (it's free), then you'll have the tooling to build a decent back-end, and perhaps even migrate the existing DB and salvage the existing data/history. – Mathieu Guindon Aug 07 '18 at 02:40
  • You would also be surprised at how low the system requirements are for running a small database - it might be worthwhile to check with some local computer places or recyclers to see if they have some equipment they would be willing to donate. – Comintern Aug 07 '18 at 02:50
  • Thanks, @Mathieu Guindon. I'll look into it. – Papa Aug 07 '18 at 02:52

1 Answers1

0

Based upon the answer from @Comintern, I overlooked that I was checking the value of "i" instead of "intPos". Stupid mistake from a guy who is tired. Thanks for all the comments; it helped me solve the issue.

Papa
  • 5
  • 5