练习-会员查询系统

实现输入号码就产生联想的功能

Private Sub ListBox1_Click()
'下拉框的值被点击时显示至文本框'
Me.TextBox1.Value = Me.ListBox1.Value
'选择好值后隐藏下拉框'
Me.ListBox1.Visible = False
End Sub

Private Sub TextBox1_Change()
'如果输入的数字大于4位'
If Len(Me.TextBox1.Value) >= 4 Then
    '每次循环前清空列表框'
    Me.ListBox1.Clear
    For i = 2 To 8
        If InStr(Sheet1.Range("i" & i), Me.TextBox1.Value) > 0 Then
            Me.ListBox1.AddItem Sheet1.Range("i" & i)
        End If
    Next
    
    '列表框中的数量大于0才显示'
    If Me.ListBox1.ListCount > 0 Then
        Me.ListBox1.Visible = True
    Else
        Me.ListBox1.Visible = False
    End If
    
        
    '循环完后显示下拉框中的内容'
    Me.ListBox1.Visible = True
    
Else

    '输入的数字时<= 4时不显示'
     Me.ListBox1.Visible = False
     

End If
End Sub

Private Sub CommandButton1_Click()
Dim rng As Range

'用find方法做
Set rng = Sheet1.Range("i1:i1000").Find(Me.TextBox1.Value)
If rng Is Nothing Then
    MsgBox "无该用户"
Else
    Me.Label2.Caption = rng.Offset(0, -6)
    Me.Label4.Caption = rng.Offset(0, -5)
    Me.Label6.Caption = rng.Offset(0, -4)
    Me.Label8.Caption = rng
    Me.Label10.Caption = rng.Offset(0, -3)
    Me.Label11.Caption = rng.Offset(0, -2)
    Me.Label12.Caption = rng.Offset(0, -1)
End If
End Sub

Private Sub ListBox1_Click()
'下拉框的值被点击时显示至文本框'
Me.TextBox1.Value = Me.ListBox1.Value
'选择好值后隐藏下拉框'
Me.ListBox1.Visible = False
End Sub

Private Sub TextBox1_Change()
'如果输入的数字大于4位'
If Len(Me.TextBox1.Value) >= 4 Then
    '每次循环前清空列表框'
    Me.ListBox1.Clear
    For i = 2 To 8
        If InStr(Sheet1.Range("i" & i), Me.TextBox1.Value) > 0 Then
            Me.ListBox1.AddItem Sheet1.Range("i" & i)
        End If
    Next
    
    '列表框中的数量大于0才显示'
    If Me.ListBox1.ListCount > 0 Then
        Me.ListBox1.Visible = True
    Else
        Me.ListBox1.Visible = False
    End If
    
        
    '循环完后显示下拉框中的内容'
    Me.ListBox1.Visible = True
    
Else
    Me.ListBox1.Clear
    '输入的数字时<= 4时不显示'
     Me.ListBox1.Visible = False
     
End If
End Sub