Option Compare Text
Function PY(ByVal rng As Range)
Dim i%, k%, str$
str = Replace(Replace(rng, " ", ""), " ", "")
For i = 1 To Len(str)
k = 1
Do Until Mid("八嚓哒妸发旮铪讥讥咔垃妈拿哦妑七然仨他哇哇哇夕丫匝咗", k, 1) > Mid(str, i, 1)
k = k + 1
Loop
PY = PY & Chr(64 + k)
Next
End Function
代码二
Private Sub CommandButton1_Click()
With CommandButton1
If .Caption = "启用" Then
.Caption = "禁用"
TextBox1.Visible = False
ListBox1.Visible = False
Else
.Caption = "启用"
End If
End With
End Sub
Private Sub ListBox1_Click()
ActiveCell = Split(ListBox1, ".")(1)
TextBox1.Visible = False
ListBox1.Visible = False
End Sub
Private Sub TextBox1_Change()
Dim rng As Range, Sh As Worksheet
Dim str$, i As Byte, j As Byte, k As Byte
Dim Dic As Object
Set Dic = CreateObject("Scripting.Dictionary")
str = TextBox1
If str = " " Or str = " " Then
TextBox1.Visible = False
ListBox1.Visible = False
Exit Sub
End If
If Len(str) = 1 Then
If IsNumeric(str) Then Exit Sub
ElseIf IsNumeric(Right(str, 2)) Then
If Len(str) = 2 Then
k = CInt(str)
str = ""
Else
k = Right(str, 2)
str = Left(str, Len(str) - 2)
End If
End If
Set Sh = Sheets("名册")
i = 1
ListBox1.Clear
For Each rng In Sh.Range("B1", Sh.[B65536].End(3))
If rng Like UCase("*" & str & "*") Then
Dic(i) = rng.Offset(, -1)
ListBox1.AddItem Format(i, "00.") & rng.Offset(, -1)
i = i + 1
End If
Next
If k > 0 Then
ActiveCell = Dic(k)
Dic.RemoveAll
TextBox1.Visible = False
ListBox1.Visible = False
End If
End Sub
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
ListBox1.Visible = False
TextBox1.Visible = False
ActiveCell(2).Select
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If CommandButton1.Caption = "禁用" Then Exit Sub
If Target.Column > 1 Or Target.Row = 1 Or Target.Cells.Count > 1 Then
ListBox1.Visible = False
TextBox1.Visible = False
Exit Sub
End If
With TextBox1
.Activate
.Visible = True
.Value = ""
.Top = Target.Top
.Left = Target.Left
.Width = Target.Width + 20
.Height = Target.Height
End With
With ListBox1
.Visible = True
.Top = Target.Offset(1).Top
.Left = Target.Left
.Width = Target.Width + 20
.Height = Target.Height * 10
End With
End Sub