Snasui Excel Forum

Code: Select all

Private Sub UserForm_Initialize()
    'On Error Resume Next
    'Call MacroScreenUpdat_F
    'Call UnProtectAllSheets

    Sheets("ReportReturn").Select
    TB21.Text = Sheets("ReportReturn").Range("C7")
    
    With Me.ListBox1
        .BoundColumn = 1
        .ColumnCount = 8
        .ColumnHeads = True
        .TextColumn = True
        If Range("C9") = "" Then
            .RowSource = "ReportReturn!C9:N9"
        Else
            .RowSource = "ReportReturn!C9:N" & Application.Match( _
                9.99999999999999E+307, Sheets("ReportReturn") _
                .Range("N1:N" & Rows.Count)) 'xlLastRow("ReportReturn")
        End If
        .ListStyle = fmListStyleOption
        .ListIndex = 0
    End With
    
End Sub

Code: Select all

Private Sub ListBox1_Change()
    Dim SourceRange As Excel.Range
    Dim Val1 As String, Val2 As String, Val3 As String

    If (ListBox1.RowSource <> vbNullString) Then
        'Get Range that the ListBox is bound to
        Set SourceRange = Range(ListBox1.RowSource)
    Else
        'Get first data row
        Set SourceRange = Range("Sheet1!A2:C2")
        Exit Sub
    End If
    
    Val1 = ListBox1.Value
    'Get the value of the second column
    Val2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize(1, 1).Value
    'Get the value of the third column
    Val3 = SourceRange.Offset(ListBox1.ListIndex, 2).Resize(1, 1).Value

    'Concatenate the three values together and display them in Label1
    'Label1.Caption = "Selected Data: " & vbNewLine & Val1 & " " & Val2 & " " & Val3
    With UserForm1
        .TextBox1 = Val1
        .TextBox2 = Val2
        .TextBox3 = Val3
        .TextBox11 = Val1
    End With
    
    'Clean Up
    Set SourceRange = Nothing
End Sub