How to "concatenate" multiple values entered by a user into the table in a Msg box?

Question

How to "concatenate" multiple values entered by a user into the table in a Msg box?

Navigation

#1 by (3 votes)

3

I'll try to summarize what I'm trying to do:

I made a userform so that my user inserts data that will feed a table, it can put "n" information and the table will be formed according to the volume of data that it populates.

After that, when it finishes and clicks END, I want another userform to open the screen with a sentence concatenating the data that was inserted, following a predetermined logic.

For example, after the inputs the worksheet looks like this:

WhatIwantisformynextuserformtobringthefollowingconcatenated:

'Test'-x"1111 | 2 | 66", "2222 | 2 | 77", "3333 | 3.88", "4444 | 3 | 99" -n

The terms that I need to add with & I can, but I do not know how to concatenate in this way the values of each column following the sequence and stopping to the point where there is no more information in the worksheet.

Another detail would be to have this separation between, between each concatenated

The section where I want to put the command to concatenate this is here:

Private Sub sair_click()
    Dim parse As String
    parse = "concatenado"
    Unload Me
    code.console = parse
    code.Show
End Sub

(after it clicks on the END button "exit" opens my userform "code" with the "parse" variable that should have this concatenated result.

After using the solution I replaced the "ShowWithMessage sub" for the call of my last Userform, because it met the demand demanded (my user is expected to copy the result to paste in another location). Perhaps it is some mistake that I made, to illustrate better, the expected sequence is as follows:

1st Userform: The user inserts the title, after clicking enter, another userform opens;

2ndUserform:Theuserentersthethreeinformationneededtopopulatethecolumnsofthetableasmanytimesasnecessary(byclickingEnteraftereachfill).WhenitfinishesitclicksFIMthatbringsthelastUserformwithatextboxcontainingtheconcatenatedresult.

InthisexampleIsimulatedthecasewhereonlyonelinewasinsertedbytheuser.

+---+------------------+----------------+-----------+||A|B|C|+---+------------------+----------------+-----------+|1|NomedaPlanilha|Teste'|||2|NodedaAula|TipodaTarefa|Agrupador||3|"1111|           | 2|             | 66"       |
|   |                  |                |           |
+---+------------------+----------------+-----------+

3rd Userform: As stated above, the final result of inserting only one complete row in the table is as follows:

Thescriptofthe"END" button is as follows, using the solution proposed in this topic:

    Private Sub sair_click()
        Dim parse As String
        Dim ws As Worksheet
        Dim UltimaLinhaPlanilha As Long, Linhas As Long, Colunas As Long
        Set ws = ThisWorkbook.Sheets("ADAE")
        UltimaLinhaPlanilha = ws.UsedRange.Rows.Count
        parse = ws.Range("B1") & " -x "

        For Linhas = 3 To UltimaLinhaPlanilha
            For Colunas = 1 To 3
                parse = parse & ws.Cells(Linhas, Colunas)
            Next Colunas
            If Linhas <> UltimaLinhaPlanilha Then parse = parse & ","
        Next Linhas
        parse = parse & " -n"
        Unload Me
        code.console = parse 'Aqui eu chamo o ultimo userform "code" com o que estará escrito dentro da caixa de texto "console" no caso a variável "parse"
        code.Show      
    End Sub

excel vba excel-vba

asked by anonymous 03.05.2018 / 21:37

1 answer

What are index hints? Remove indices from array

score 3 · Accepted Answer

Forms

I understand that you will have two forms Userform1 and Userform2

In Userform1 there is a button that you click to exit.
In Userform2 the message will be displayed.

So, for tests, the following forms are created:

Userform1 has the button named CommandButton1 :

Userform2hasaNametagLabel1:

Data

Withthedatainthespreadsheet(tab)namedPlanilha1thisway:

+---+------------------+----------------+-----------+||A|B|C|+---+------------------+----------------+-----------+|1|NomedaPlanilha|Teste'|||2|NodedaAula|TipodaTarefa|Agrupador||3|"1111|           | 2|             | 66"       |
| 4 | "2222|           | 2|             | 77"       |
| 5 | "3333|           | 3|             | 88"       |
| 6 | "4444|           | 3|             | 99"       |
|   |                  |                |           |
+---+------------------+----------------+-----------+

Code

Another form will be used to display the data, because the MsgBox has a maximum limit of 1024 characters. And the OP asked for form:

What I want is for my next userform to bring the following concatenated:

The code to accomplish this is as follows:

Option Explicit
Private Sub CommandButton1_Click()
    Dim parse As String
    Dim ws As Worksheet
    Dim UltimaLinhaPlanilha As Long, Linhas As Long, Colunas As Long
    Set ws = ThisWorkbook.Sheets("Planilha1")
    UltimaLinhaPlanilha = ws.UsedRange.Rows.Count
    parse = ws.Range("B1") & " -x "

    For Linhas = 3 To UltimaLinhaPlanilha
        For Colunas = 1 To 3
            parse = parse & ws.Cells(Linhas, Colunas)
        Next Colunas
        If Linhas <> UltimaLinhaPlanilha Then parse = parse & ","
    Next Linhas
    parse = parse & " -n"
    ShowWithMessage parse
    Unload Me
End Sub

'https://stackoverflow.com/a/23313840/7690982
Public Sub ShowWithMessage(msg As String)
    UserForm2.Label1.Caption = msg
    UserForm2.Show
End Sub

The code is inserted inside Userform1.

Result

Explanation

AfterthebuttonisclickedthecodeinsidePrivateSubCommandButton1_Click()isexecuted.WheretheparsestringiscreatedandthemessageiscalledwithPublicSubShowWithMessage(msgAsString)

SpreadsheetStatement

Setws=ThisWorkbook.Sheets("Planilha1")

Last Line

The last row of the worksheet is found with UltimaLinhaPlanilha = ws.UsedRange.Rows.Count and more information can be found in this question

String

The parse string is created with the following concatenation:

Title is entered with parse = ws.Range("B1") & " -x "
Loops are then made from Line 3 to the last line filled in the worksheet. In each row the loop from column 1 or A to column 3 or C, after looping in all columns, the comma is inserted. Each loop inserts the data from the worksheet into the parse string.
Finally inserts the "-n"

Call Sub

Finally the Sub is called with ShowWithMessage parse , where the parse variable is inserted as an input variable of type String.

Sub ShowWithMessage

First change the Caption property with the string entered with UserForm2.Label1.Caption = msg and then open Userform2.