|
楼主 |
发表于 2020-2-20 19:46
|
显示全部楼层
我现在的代码是这样的
Private Sub unerForm_Initialize()
If Me.ListBox1.Text = "" Then Me.ListBox1.Visible = False
' Me.ListBox1.visible = False ' Else Me.ListBox1.Visible = true
End Sub
Private Sub 电话卡号_Change()
If Len(Me.电话卡号.Value) >= 1 Then
Me.ListBox1.Clear
inputText = 电话卡号.Text
arr = getKuanhao(inputText) '通过输入框文字 匹配list内容
Me.ListBox1.List = arr
Me.ListBox1.MatchEntry = fmMatchEntryNone
Me.ListBox1.Visible = True 'False
Else
Me.ListBox1.Visible = False 'True'False
End If
End Sub
Private Sub ListBox1_Click()
Me.电话卡号.Value = Me.ListBox1.Value
Me.ListBox1.Visible = False
End Sub
Private Sub CommandButton4_Click()
Me.TextBox1.Value = ""
Me.会员姓名.Value = ""
Me.性别.Value = ""
Me.卡类型.Value = ""
Me.累计充值.Value = ""
Me.累计划卡.Value = ""
Me.卡内余额.Value = ""
For i = 3 To Sheet1.Range("b65536").Row
If Sheet1.Range("a" & i) = Me.电话卡号.Value Then
Me.TextBox1.Value = Sheet1.Range("a" & i)
Me.会员姓名.Value = Sheet1.Range("b" & i)
Me.性别.Value = Sheet1.Range("c" & i)
Me.卡类型.Value = Sheet1.Range("d" & i)
Me.累计充值.Value = Sheet1.Range("e" & i)
Me.累计划卡.Value = Sheet1.Range("f" & i)
Me.卡内余额.Value = Sheet1.Range("g" & i)
UserForm4.累计充值.Enabled = False
UserForm4.累计划卡.Enabled = False
UserForm4.卡内余额.Enabled = False
End If
Next
End Sub
Private Sub CommandButton1_Click() '编辑
MsgBox ("请修改记录!")
UserForm4.TextBox1.Enabled = True
UserForm4.会员姓名.Enabled = True
UserForm4.性别.Enabled = True
UserForm4.卡类型.Enabled = True
UserForm4.CommandButton3.Enabled = True '保存记录
End Sub
Function getKuanhao(inputText)
Dim w
w = Worksheets("会员信息").[a1].CurrentRegion
Dim i
Dim k
i = 2
Dim myDic As Object
Set myDic = CreateObject("scripting.dictionary")
For i = 2 To UBound(w)
If Not myDic.exists(w(i, 1)) And InStr(w(i, 1), inputText) > 0 Then
myDic.Add w(i, 1), " "
End If
Next
If myDic.Count = 0 Then '如果字典为空不处理的话 后面会出现数据类型不匹配
myDic.Add "没有这个卡号", ""
End If
getKuanhao = Application.Transpose(myDic.keys)
End Function |
|