Input Box

[Abgar]

Hi guys,

Hoping you could help me.
Im making a User Form with an input box that the user will copy rows of data into.
I want to have a command button, that when the data has been copied, and the user hits the command button, the input box text will change to display the result of a formula....
i.e. (sorry - no screenshot, so imagination required)

The data that the user copies in to the Input box (just as standard text) is:
aaTest1
abTest1
acTest1

And then when the command button is pressed, i would like the userform to run a macro that changes the text in the input box, to the RESULT of a formula for each line of text.
So that after the command button has been pressed, i want the Input Box to now display:
aa
ab
ac

In this example, the formula i want performed on each row of text is =LEFT(line1,2)

But i want the formula to be editible in the macro, as i will be using various formulas at different times.

Can anyone help me with this?
Alternatively, if i have to have a second Input Box that is purely for displaying the output, this is also acceptable.

Thanks so much if anyone can help me with this guys

Cheers

 

[capparfrank]

Try this:

Private Sub CommandButton1_Click()
    Dim stringList As Variant  'a variant to be used as an array
    Dim resultString As String  ' a string variable where we'll build our output
    
    resultString = "" 'always a good idea to initialize!
    stringList = Split(TextBox1.Text, vbCrLf) 'split the input box contents using the newlines as delimiters
    
    For Each s In stringList 'iterate through the array resulting from the split function above
    
        s = Left(s, 2) 'perform your work on each value
        resultString = resultString + s + vbCrLf
        
    Next s 'next iteration
    
    resultString = Left(resultString, Len(resultString) - 2) ' trim that last linefeed away (vbCrLF is 2 characters!)
    
    TextBox1.Text = resultString 'fill your "input" box new output string
    
End Sub

remember to set your text box on the form to multiline= true

 

[Abgar]

Wow that worked fantastically

Thanks so much for that.

The only issue i'm having is manipulating the formula to use....

The first example that i want to have uses the below formula:

=CONCATENATE("Input1 "&(MID(A1,FIND("|",A1)-3,7)&" /Input1"))

I've tried implementing that as follows, however to no avail....

s.Formula = "=CONCATENATE(""Input1 ""&(MID(A1,FIND(""|"",A1)-3,7)&"" /Input1""))"

But if someone can enlighten me on the formula construct and how to implement, so that i can adapt the formula if required.

Any ideas?

Thanks so much

 

[capparfrank]

The excel functions you are using don't translate into VBA. Instead of =Find, you'll want to use InStr(). Rather than =Concatenate, the '&' operater will do all that you need for string concatenations (e.g. "a" & "b" => "ab")

Here's the code you need, plus a little error handling for when the user hasn't given you the right stuff to work with.

Private Sub CommandButton1_Click()
    Dim stringList As Variant  'a variant to be used as an array
    Dim resultString As String  ' a string variable where we'll build our output
 
    resultString = "" 'always a good idea to initialize!
    stringList = Split(TextBox1.Text, vbCrLf) 'split the input box contents using the newlines as delimiters
 
    For Each s In stringList 'iterate through the array resulting from the split function above
        If (Len(s) > 7 And InStr(1, s, "|") - 3 > 0) Then 'the Mid function below will fail if our parameter becomes less than zero or the total string can't meet our requirement of 7.  Handle this error condition
            s = "Input1 " & Mid(s, InStr(1, s, "|") - 3, 7) & " /Input1"
        Else
            s = "ERR: '" & s & "' Does not meet input specifications (no | or insufficient length)" 'tell the user what went wrong
        End If
 
        resultString = resultString + s + vbCrLf
 
    Next s 'next iteration
 
    resultString = Left(resultString, Len(resultString) - 2) ' trim that last linefeed away (vbCrLF is 2 characters!)
 
    TextBox1.Text = resultString 'fill your "input" box new output string
 
End Sub

 

[Abgar]

Thats perfect

Thanks so much for that