Snasui Excel Forum

Code: Select all

Rem:àÅ×Í¡ÃÒ¡ÒÃã¹ Listbox ãËéáÊ´§¢éÍÁÙÅ㹿ÍÃìÁ
Private Sub lstSearch_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
wsFormEdit.showRecord Sheet1.Range(lstSearch.Value)
Unload Me
FormAdd_Data.Hide
wsFormEdit.Activate
End Sub
Rem:¡´»ØèÁ Enter ã¹ Listbox  ãËé»Ô´¿ÍÃìÁ¡ÅѺ价Õè¿ÍÃìÁ
Private Sub lstSearch_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii = 13 Then
    If lstSearch.ListCount = 0 Then Exit Sub
    Unload Me
    wsFormEdit.Activate
    End If
End Sub

Private Sub OptionButton1_Change()
If OptionButton1.Value = True Then
txtSearch.Enabled = True
txtSearch.SetFocus
Else
txtSearch.Enabled = False
End If
End Sub
Private Sub OptionButton2_Change()
If OptionButton2.Value = True Then
txtName.Enabled = True
txtName.SetFocus
Else
txtName.Enabled = False
End If
End Sub
Private Sub UserForm_Initialize()
txtSearch.Enabled = False
txtName.Enabled = False
With lstSearch
.ColumnCount = 12
.ColumnWidths = "0,50,50,50,80,35,30,30,50,50,60,80,50"
End With
End Sub
Private Sub txtSearch_keyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case 13
    If showList(txtSearch.Text) Then lstSearch.ListIndex = -1
End Select
End Sub
Private Function showList(c As String) As Boolean
Dim rg As Range
c = Trim(c)
If c = "" Then showList = False: Exit Function
lstSearch.Clear
With Sheet1.Columns(3)
    Set rg = .Find(c, LookIn:=xlValues, LookAt:=xlPart)
    If rg Is Nothing Then showList = False: Exit Function
    cAddr = rg.Address
    i = 0
    Do
        If rg.Row > 1 Then
            With lstSearch
                .AddItem rg.Offset(0, -2).Address
                .List(i, 1) = rg.Offset(0, -2).Value
                .List(i, 2) = rg.Offset(0, -1).Value
                .List(i, 3) = rg.Offset(0, 0).Value
                .List(i, 4) = rg.Offset(0, 1).Value
                .List(i, 5) = rg.Offset(0, 2).Value
                .List(i, 6) = rg.Offset(0, 3).Value
                .List(i, 7) = rg.Offset(0, 4).Value
                .List(i, 8) = rg.Offset(0, 5).Value
                .List(i, 9) = rg.Offset(0, 6).Value
                 .List(i, 10) = rg.Offset(0, 7).Value
               
                End With
                i = i + 1
                End If
                Set rg = .FindNext(rg)
            Loop Until rg Is Nothing Or rg.Address = cAddr
            End With
            showList = True
End Function