CODEWITHSUNDEEP
excelvbamacos
K Lee
K Lee

Reputation: 493

How can I show multiple cells in the one textbox of the userform in Excel VBA?

I am now trying to fill the textbox in the UserForm(txtboxTransactionHistory) by the multiple cells data EXCEL when I click the button(CommandButton1_Click)

I have tried to copy the row until the employed cell, then hoped to paste to the UserForm TextBox directory.

I am not sure why does it show TURE instead of Row I copied, moreover how can I implemented that I want to see?

Environment: Excel for 2016 & Excel for Mac

The photo1 is the data of the sheet(TransactionHistory)

The photo2 is what I want to implement like

The photo3 is how it works right now

photo1 photo2 photo3

Public Sub CommandButton1_Click()

Dim i As Long, j As Long
Set ws = Sheets("TransactionHistory")
Set ws2 = Sheets("Test")
i = 2
Do While ws.Cells(i, 1).Value <> ""
ws.Rows(i).Copy
   
    i = i + 1
Loop
Me.txtboxTransactionHistory = Rows(i).PasteSpecial(xlPasteAll)
 
End Sub

↓Class Module

 Public CryptCurrency As String
 Public ExchangeRate As Double
 Public Quantity As Double
 Public TransactionDate As Date
 Public transactionType As transactionType
 
 Public Function tostring() As String
 tostring = CryptCurrency & " " & transcriptionType & " " & "quantity: " & Quantity & "rate: " & ExchangeRate
 End Function

====For later viewers====

Here is the Setting of TextBox to make a new line, for instance

With TextBox1
        .MultiLine = True       
        .EnterKeyBehavior = True  
End With

Upvotes: 0

Views: 1738

Answers (1)

Simon
Simon

Reputation: 1375

You need to do something like this:

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False

Dim i As Long, j As Long, ws As Worksheet, ws2 As Worksheet, Info As String
Set ws = Sheets("TransactionHistory")
Set ws2 = Sheets("Test")
i = 2
Do While ws.Cells(i, 1).Value <> ""

    With ws
        'Info = Format(.Range("A" & i), "dd.mm.yyyy") & " " & .Range("B" & i) & " " & .Range("C" & i) & " Quantity: " & .Range("D" & i) & " Rate: " & .Range("E" & i)
        'Info = Join(Application.Transpose(Application.Transpose(.Range("A" & i, "E" & i).Value)), " ") 'Use this line if you just want the exact cells as they look in the sheet
        Info = tostring(.Range("A" & i), .Range("B" & i), .Range("C" & i), .Range("D" & i), .Range("E" & i))
    End With

    Me.txtboxTransactionHistory.Text = Me.txtboxTransactionHistory.Text & Info & vbCr
    i = i + 1
Loop

Application.ScreenUpdating = True

End Sub

Depending on which way you go I added both options to it.

You need to also make sure your textbox has MultiLine set to TRUE in the properties of the textbox for it to work.

To update. I don't know if you labelled the variables for your module elsewhere. Otherwise it should be like:

Public Function tostring(TransactionDate As Date, TransactionType As String, CryptCurrency As String, Quantity As Double, ExchangeRate As Double) As String
 tostring = TransactionDate & " " & TransactionType & " " & CryptCurrency & " " & transcriptionType & " " & " quantity: " & Quantity & " rate: " & ExchangeRate
 End Function

Then you can utilize my updated code above, this includes changing the vbNewLine to vbCr to cater for Mac.

Upvotes: 1

Related Questions