+ Reply to Thread
Results 1 to 5 of 5

get data from multiple rows into 1 textbox

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    get data from multiple rows into 1 textbox

    Hi, I'm using this code to get data from multiple rows with the same criteria into 1 textbox, but what it is doing is only taking the last row of that criteria and putting it into the textbox. how do I change it so that the correct number of rows of data is reflected in the textbox?

    Private Sub ComboBox2_Change()
        Dim ws1 As Worksheet
        Dim ws2 As Worksheet
        Dim iRow As Long
        
        Set ws1 = ThisWorkbook.Sheets("Case Log")
        Set ws2 = ThisWorkbook.Sheets("Print page")
        TextBox18.Text = ""
        TextBox8.Text = ""
        TextBox9.Text = ""
        
        With ws2.Range("A5:C100")
        .ClearContents
        End With
        
        iRow = ws2.Cells(Rows.Count, 1) _
        .End(xlUp).Offset(1, 0).Row
        
        wsLR = ws1.Cells(Rows.Count, 1).End(xlUp).Row
            For x = 2 To wsLR
            If ws1.Cells(x, 1) = ComboBox2.Value Then
            ws2.Cells(3, 3) = ComboBox2.Value
            ws2.Cells(4, 3) = CDate(ws1.Cells(x, 7))
            With TextBox18
            .Text = "General Comments:" & vbCr & ws1.Cells(x, 10).Text & vbCr & vbCr & "Sample / Type / Marking / Mortuary / Date & Time Received" & vbCr & ws1.Cells(x, 2).Text & " " & ws1.Cells(x, 3).Text & " " & ws1.Cells(x, 4).Text & " " & ws1.Cells(x, 5).Text & " " & ws1.Cells(x, 7).Text & " " & ws1.Cells(x, 8).Text & vbCr
            ws2.Cells(iRow + 1, 3) = ws1.Cells(x, 2).Text
            ws2.Cells(iRow + 2, 3) = ws1.Cells(x, 4).Text
            ws2.Cells(iRow + 3, 3) = CDate(ws1.Cells(x, 7))
            
            iRow = iRow + 4
            
            End With
            With TextBox8
            .Text = ws1.Cells(x, 6).Text
            End With
            With TextBox9
            .Text = ws1.Cells(x, 9).Text
            End With
            End If
            Next x
            
        If ComboBox2.Value = "" Then
            ws2.Range("A:B").Value = ""
        Else
            ws2.Range("A1").Value = "DNA Profiling Laboratory, HSA"
            ws2.Range("A3").Value = "Case No"
            ws2.Range("B3").Value = ":"
            ws2.Range("A4").Value = "Date Created"
            ws2.Range("B4").Value = ":"
        End If
    
        If ws2.Range("C6") = "" Then
        ws2.Range("A5:B8").Value = ""
        Else
        ws2.Range("A5").Value = "DNA Profiling Laboratory, HSA"
        ws2.Range("A6").Value = "Sample No"
        ws2.Range("B6").Value = ":"
        ws2.Range("A7").Value = "Marking"
        ws2.Range("B7").Value = ":"
        ws2.Range("A8").Value = "Received Date"
        ws2.Range("B8").Value = ":"
        End If
        
        If ws2.Range("C10") = "" Then
        ws2.Range("A9:B12").Value = ""
        Else
        ws2.Range("A9").Value = "DNA Profiling Laboratory, HSA"
        ws2.Range("A10").Value = "Sample No"
        ws2.Range("B10").Value = ":"
        ws2.Range("A11").Value = "Marking"
        ws2.Range("B11").Value = ":"
        ws2.Range("A12").Value = "Received Date"
        ws2.Range("B12").Value = ":"
        End If
        
    End Sub

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: get data from multiple rows into 1 textbox

    Which textboxes should show multiple items and how should they be displayed?
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: get data from multiple rows into 1 textbox

    hi norie, It is textbox18.

    "General Comments:" & vbCr & ws1.Cells(x, 10).Text & vbCr & vbCr & "Sample / Type / Marking / Mortuary / Date & Time Received" & vbCr & ws1.Cells(x, 2).Text & " " & ws1.Cells(x, 3).Text & " " & ws1.Cells(x, 4).Text & " " & ws1.Cells(x, 5).Text & " " & ws1.Cells(x, 7).Text & " " & ws1.Cells(x, 8).Text & vbCr
    I would like to have "Sample / Type / Marking / Mortuary / Date & Time Received" as a header, then line break, followed by the data. So, it should look something like this:

    Sample / Type / Marking / Mortuary / Date & Time Received
    Sample1-001 hair 12345 M23456 12/10/16 11.15am
    Sample1-002 tooth 33445 M34543 12/10/16 11.15am

    hope that explains it

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: get data from multiple rows into 1 textbox

    Try this.
    With TextBox18
            .Text = .Text & "General Comments:" & vbCr & ws1.Cells(x, 10).Text & vbCr & vbCr & "Sample / Type / Marking / Mortuary / Date & Time Received" & vbCr & ws1.Cells(x, 2).Text & " " & ws1.Cells(x, 3).Text & " " & ws1.Cells(x, 4).Text & " " & ws1.Cells(x, 5).Text & " " & ws1.Cells(x, 7).Text & " " & ws1.Cells(x, 8).Text & vbCr
            ws2.Cells(iRow + 1, 3) = ws1.Cells(x, 2).Text
            ws2.Cells(iRow + 2, 3) = ws1.Cells(x, 4).Text
            ws2.Cells(iRow + 3, 3) = CDate(ws1.Cells(x, 7))
            
            iRow = iRow + 4
            
    End With

  5. #5
    Forum Contributor
    Join Date
    05-06-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    361

    Re: get data from multiple rows into 1 textbox

    How do I make the scrollbars on the textbox appear when I need them to? the info exceeds the size of the textbox, and even though I set scrollbars to both, it doesn't appear

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. multiline textbox data to be pasted in multiple rows in vba
    By VaibhavAr in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-13-2016, 03:03 AM
  2. Export (activeX) TextBox Values to multiple Rows
    By sfirita in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-20-2016, 12:17 PM
  3. [SOLVED] Return value of multiple rows in an Excel sheet into one textbox, with tabs between values
    By Martijn79 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-13-2014, 04:41 PM
  4. Split a long text in a userform textbox to multiple rows on a worksheet
    By newbi004 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-31-2012, 09:03 AM
  5. Search duplicate entries and displaying multiple rows on textbox
    By weeliang88 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-26-2012, 10:54 PM
  6. Textbox with multiple columns and rows, Heading problem.
    By ali84pk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-18-2011, 03:52 AM
  7. Textbox with multiple columns and rows using data from another sheet.
    By ali84pk in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-17-2011, 10:29 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1