Snasui Excel Forum

Code: Select all

Private Sub UserForm_Initialize()
Dim i As Long
For i = 1 To Sheet2.Range("A301").End(xlUp).Offset(1, 0).Row
Me.ComboBox1.AddItem Sheet2.Cells(i, 1).Value
Next i
End Sub

Code: Select all

Private Sub ComboBox1_Change()
Sheet1.Range("N1") = ComboBox1.Text
Dim a
Dim i As Long
Me.ComboBox1.Text = StrConv(Me.ComboBox1.Text, vbProperCase)
Me.ListBox1.Clear
For i = 3 To Application.WorksheetFunction.CountA(Sheet1.Range("D:D"))
a = Len(Me.ComboBox1.Text)
If Left(Sheet1.Cells(i, 1).Value, a) = Left(Me.ComboBox1.Text, a) Then
Me.ListBox1.AddItem Sheet1.Cells(i, 1).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet1.Cells(i, 4).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 2) = Sheet1.Cells(i, 5).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = Sheet1.Cells(i, 6).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = Sheet1.Cells(i, 7).Value
End If
Next i

Code: Select all

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    ActiveCell.Value = ListBox1.List(ListBox1.ListIndex)
    ActiveCell.Offset(, 1).Value = ListBox1.List(ListBox1.ListIndex, 1)
    ActiveCell.Offset(, 2).Value = ListBox1.List(ListBox1.ListIndex, 2)
    ActiveCell.Offset(, 3).Value = ListBox1.List(ListBox1.ListIndex, 3)
    ActiveCell.Offset(, 4).Value = ListBox1.List(ListBox1.ListIndex, 4)
    ListBox1.Value = ""
    ActiveCell.Offset(1, 0).Select
End Sub

Code: Select all

Private Sub UserForm_Initialize()
Dim i As Long
For i = 1 To Application.CountIf(Sheet2.Range("a:a"), "?*")
Me.ComboBox1.AddItem Sheet2.Cells(i, 1).Value
Next i
End Sub

Code: Select all

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    ActiveCell.Value = ListBox1.List(ListBox1.ListIndex)
    ActiveCell.Offset(, 1).Value = ListBox1.List(ListBox1.ListIndex, 1)
    ActiveCell.Offset(, 2).Value = ListBox1.List(ListBox1.ListIndex, 2)
    ActiveCell.Offset(, 3).Value = ListBox1.List(ListBox1.ListIndex, 3)
'    ActiveCell.Offset(, 4).Value = ListBox1.List(ListBox1.ListIndex, 5)
    ListBox1.Value = ""
    ActiveCell.Offset(1, 0).Select
End Sub

Code: Select all

Private Sub ComboBox1_Change()
Sheet1.Range("N1") = ComboBox1.Text
Dim a
Dim i As Long
Me.ComboBox1.Text = StrConv(Me.ComboBox1.Text, vbProperCase)
Me.ListBox1.Clear
For i = 3 To Application.WorksheetFunction.CountA(Sheet1.Range("D:D"))
a = Len(Me.ComboBox1.Text)
If Left(Sheet1.Cells(i, 1).Value, a) = Left(Me.ComboBox1.Text, a) Then
Me.ListBox1.AddItem Sheet1.Cells(i, 4).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet1.Cells(i, 5).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 2) = Sheet1.Cells(i, 6).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 3) = Sheet1.Cells(i, 7).Value
'Me.ListBox1.List(ListBox1.ListCount - 1, 4) = Sheet1.Cells(i, 7).Value
End If
Next i

End Sub