Validation: Allowing one specific word only once can be input in textbox in VBA Excel

Question

I have a 4 different words (financial, location, course, professor) that can be inputted in a textbox, but each word must be used only once per input in the textbox.

For example, I enter a sentence in the textbox like this: "I have a problem with financial because my family is facing a financial problem" the code below processes this sentence into split text.

What I want to do for validation is to inform the user (maybe through msgbox) something like:

"Error - you must used financial only once in a sentence."

In addition, if course, location and professor used more than once in a sentence should also give a msgbox.

Private Sub CommandButton1_Click()
Call SplitText
End Sub
Sub SplitText()
    Dim WArray As Variant
    Dim TextString As String
    TextString = TextBox1
    WArray = Split(TextBox1, " ")
    If (TextString = "") Then
    MsgBox ("Error: Pls Enter your data")
    Else

    With Sheets("DatabaseStorage")
        .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(UBound(WArray) + IIf(LBound(WArray) = 0, 1, 0)) = Application.Transpose(WArray)
    End With

    MsgBox ("Successfully inserted")

    End If

End Sub

This is not a code snippit, don't format it as such. VBA isn't ment to be ran inside a snippit. — Luuklag, Sep 05 '17 at 14:33
What is the problem that you get when people input financial twice in a textbox? You could easily check if the word is present in their entry, the amount of times it is present shouldn't matter. — Luuklag, Sep 05 '17 at 14:34
because if the people input financial twice it will be counted also because the split text purposes is to get the word that i want to get and count it in distinct which means that i am getting the number of count to display most occurence word based in the count function but if the user input many financial in a sentence then there's a possibility that the count is not accurate. — Pablo, Sep 05 '17 at 14:36
I think it would be more user friendly if you simply removed duplicates from your array before posting it to your database. You could take a look here: https://stackoverflow.com/questions/11870095/vba-remove-duplicates-from-array — Luuklag, Sep 05 '17 at 14:41

score 1 · Accepted Answer · answered Sep 05 '17 at 15:29

1

Try this:

Private Sub CommandButton1_Click()
    Call SplitText
End Sub

Sub SplitText()
    Dim sentence As String
    Dim mycount As Long

    sentence = InputBox("Enter the sentence")
    mycount = UBound(Split(sentence, "financial"))
    If mycount > 1 then
        Msgbox "Error - you must used financial only once in a sentence"
    End if

    'Here the rest of the code you need

End Sub

Hope it helps.

answered Sep 05 '17 at 15:29

jsanchezs

1,992
3
25
51

Hi sir it works the msgbox but every time i try to save to my database it returns error :/ – Pablo Sep 05 '17 at 15:45
@RaeIan Remember you´re using a variable called WArray in your code, that variable is not in mine because i didn't use your split method, it's just you replace it when save according to what you need there. – jsanchezs Sep 05 '17 at 15:53

Validation: Allowing one specific word only once can be input in textbox in VBA Excel

1 Answers1