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

Question

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, he can put "n" information and the table will form according to the volume of data he fills.

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

For example, after the inputs the spreadsheet looked like this:

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

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

The terms I need to add with & I can, but I don’t 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 spreadsheet.

Another detail would be to have this separation by , 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 variable "parse" that should have this result concatenated.

---

After using the presented solution I replaced the "sub Showwithmessage" for the call of my last Userform, as it met the required demand (it is expected that my user can copy the result to paste elsewhere). Maybe it’s some mistake I made, to illustrate it better, the expected sequence is as follows:

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

2º Userform: The user inserts the three information needed to fill the table columns as many times as needed (Clicking Enter after each fill). At the end of it click on END which brings the last Userform with a text box containing the concatenated result.

In this example I simulated the case where only one line was inserted by the user.

+---+------------------+----------------+-----------+
|   |        A         |       B        |     C     |
+---+------------------+----------------+-----------+
| 1 | Nome da Planilha | Teste'         |           |
| 2 | Node da Aula     | Tipo da Tarefa | Agrupador |
| 3 | "1111|           | 2|             | 66"       |
|   |                  |                |           |
+---+------------------+----------------+-----------+

3º Userform: As said above, the final result of inserting only one complete row in the table was this way:

The script of the "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

Answer 1

Forms

From what I understand you will have two forms Userform1 and the Userform2

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

Therefore, for the tests, the following forms are created:

Userform1 has the name button CommandButton1:

Userform2 has a Name Label Label1:

Dice

With the data in the worksheet (tab) name Planilha1 in this way:

+---+------------------+----------------+-----------+
|   |        A         |       B        |     C     |
+---+------------------+----------------+-----------+
| 1 | Nome da Planilha | Teste'         |           |
| 2 | Node da Aula     | Tipo da Tarefa | 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 Msgbox has a limit of at most 1024 characters. And the OP requested per 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.

Upshot

Explanation

After clicking the button the code inside Private Sub CommandButton1_Click() is executed. Where the string parse is created and the message is called with the Public Sub ShowWithMessage(msg As String)

Spreadsheet Statement

Set ws = ThisWorkbook.Sheets("Planilha1")

Last Line

The last line of the spreadsheet 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 inserted with parse = ws.Range("B1") & " -x "
• Then loops are made from Line 3 to the last line filled in the spreadsheet. In each row the loop from column 1 or A to column 3 or C, after the loop in all columns, the comma is inserted. Each loop inserts the sheet data into the parse string.
• Finally insert 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 sets the Caption property with the string inserted with UserForm2.Label1.Caption = msg and then opens Userform2.