Excel Forum

Code: Select all

Private Sub TextBox1_Change()
Dim i As Long
TextBox1.Text = StrConv(TextBox1.Text, vbProperCase)
ListBox1.Clear
For i = 15 To Application.WorksheetFunction.CountA(Sheet1.Range("M:M"))
M = Len(TextBox1.Text)
If Left(Sheet1.Cells(i, 13).Value, M) = Left(TextBox1.Text, M) Then
ListBox1.AddItem Sheet1.Cells(i, 13).Value
ListBox1.List(ListBox1.ListCount - 1, 13) = Sheet1.Cells(i, 13).Value
End If
Next i
End Sub

Code: Select all

Private Sub CommandButton1_Click()
    Dim r As Long
    With Sheets("sheet1")
        For r = 0 To ListBox1.ListCount - 1
            If ListBox1.Selected(r) Then
                .Range("C" & .Rows.Count).End(xlUp).Offset(1, 0).Value = ListBox1.List(r)
            End If
        Next r
    End With
End Sub

Private Sub TextBox1_Change()
    Dim r As Range, rAll As Range
    If TextBox1.Text <> "" Then
        ListBox1.Clear
        With Sheets("Sheet1")
            Set rAll = .Range("m15", .Range("m" & .Rows.Count).End(xlUp))
        End With
        For Each r In rAll
            M = Len(TextBox1.Text)
            If Left(r.Value, M) = Left(TextBox1.Text, M) Then
                ListBox1.AddItem r.Value
            End If
        Next r
    End If
End Sub

Code: Select all

Private Sub CommandButton1_Click()
    Dim r As Long
    With Sheets("sheet1")
        For r = 0 To ListBox1.ListCount - 1
            If ListBox1.Selected(r) Then
                .Range("C15").Offset(Application.CountA(.Range("C15:c" & .Rows.Count).End(xlUp).Offset(1, 0))).Value = ListBox1.List(r)
            End If
        Next r
    End With
End Sub

Code: Select all

Private Sub CommandButton1_Click()
    Dim r As Long
    With Sheets("sheet1")
        If .Range("c25").Value <> "" Then
            MsgBox "Not record"
            Exit Sub
        End If
        For r = 0 To ListBox1.ListCount - 1
            If ListBox1.Selected(r) Then
                .Range("C26").End(xlUp).Offset(1, 0).Value = ListBox1.List(r)
            End If
        Next r
    End With
End Sub