Userform to data table to text box with multiple lines?

inkbird1

Board Regular
Joined
Apr 21, 2020
Messages
51
Hi everyone, I have a userform used for data entry that places the values into a data table with the corresponding column (date, time, etc) I then have a listbox in my worksheet that pulls he table data for the day and displays it. The problem is that the last column goes past the list box, and as I have since found out, you can't have multline on a listbox. So desperately need an alernative (not mouse over)

Rather than have the worksheet listbox display
Date | Time | Comment|

Have a text box that displays
Date
Time
Comment

My questions are

1 - How do I pull the code into the text box in the latter format
2 - There may be multiple entrie for that day, how do I accomodate this

Thank you very much for your help current source code for the Listbox

VBA Code:
Private Sub Worksheet_Activate()
    
    Dim arrIncidents As Variant
    Dim arrActive As Variant
    Dim lngRow As Long
    Dim cnt As Long
    
    arrIncidents = Sheets("Incidents").Range("A1").CurrentRegion.Value
    
    ReDim arrActive(1 To 6, 1 To UBound(arrIncidents, 1))
    
    For lngRow = 2 To UBound(arrIncidents, 1)
        If arrIncidents(lngRow, 1) = Range("B18").Value Then
            cnt = cnt + 1
            arrActive(1, cnt) = Format(arrIncidents(lngRow, 1), "Long Date")
            arrActive(2, cnt) = Format(arrIncidents(lngRow, 2), "hh:mm")
            arrActive(3, cnt) = arrIncidents(lngRow, 3)
            arrActive(4, cnt) = arrIncidents(lngRow, 4)
            arrActive(5, cnt) = arrIncidents(lngRow, 5)
            arrActive(6, cnt) = arrIncidents(lngRow, 6)
        End If
    Next lngRow
    
    If cnt > 0 Then
        ReDim Preserve arrActive(1 To 6, 1 To cnt)
        With ListBox100
            .ColumnCount = 6
            .Column = arrActive
            .ColumnWidths = "200,100,150,200,100,300"
            .ColumnHeads = False
        End With
        Application.ScreenUpdating = True

    End If
    
End Sub
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,215,219
Messages
6,123,692
Members
449,117
Latest member
Aaagu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top