|
中文首字母联想自适应多层级菜单
本帖最后由 YZC51 于 2019-12-15 10:53 编辑
谢谢老师分享!学习啦
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge > 1 Then GoTo 100 ': Exit Sub
If Target.Column <> 3 Or Target.Row < 2 Then GoTo 100 ': Exit Sub
With Me.ListBox1
.Visible = True
.Top = Target(2, 2).Top
.Left = Target.Left + Target.Width
.Width = Target.Width * 5 '宽度
.Height = Target.Height * 8 '高度
End With
With Me.TextBox1
.Visible = True
.Top = Target.Top + 1
.Left = Target.Left + 1
.Width = Target.Width - 2
.Height = Target.Height - 1 ' + 10
.Activate
End With
Exit Sub
100:
Me.ListBox1.Clear
Me.TextBox1 = ""
Me.ListBox1.Visible = False
Me.TextBox1.Visible = False
End Sub
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim i&, Arr, dm$, n&, j&, Arr1()
Dim Language As Boolean
Dim myStr As String
With Sheets("Sheet2")
Arr = .[a1].CurrentRegion
End With
With Me.TextBox1
If Me.TextBox1 <> "" Then '当字符为英文大写时拼音输入,否则为中文输入
If Asc(Mid$(.Value, 1)) < 91 Then .IMEMode = 0 Else .IMEMode = 12
End If
For i = 1 To Len(.Value)
If Asc(Mid$(.Value, i, 1)) > 255 Or Asc(Mid$(.Value, i, 1)) < 0 Then
Language = True
myStr = myStr & Mid$(.Value, i, 1)
Else
myStr = myStr & LCase(Mid$(.Value, i, 1))
End If
Next
End With
n = 1
Me.ListBox1.Clear
If myStr <> "" Then
For i = 2 To UBound(Arr)
If Language <> True Then
dm = py(Arr(i, 1))
If InStr("|" & dm, "|" & myStr) Then
j = j + 1
ReDim Preserve Arr1(1 To j)
For jj = 1 To UBound(Arr, 2)
Arr1(j) = Arr1(j) & Arr(i, jj) & vbTab
Next
End If
Else
If InStr("|" & Arr(i, 1), "|" & myStr) > 0 Then
j = j + 1
ReDim Preserve Arr1(1 To j)
For jj = 1 To UBound(Arr, 2)
Arr1(j) = Arr1(j) & Arr(i, jj) & vbTab
Next
End If
End If
Next i
Else
For i = 2 To UBound(Arr)
j = i - 1
ReDim Preserve Arr1(1 To j)
For jj = 1 To UBound(Arr, 2)
Arr1(j) = Arr1(j) & Arr(i, jj) & vbTab
Next
Next i
End If
With ListBox1
.Clear
If j > 0 Then .List = Arr1
End With
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Arr = Split(ListBox1, vbTab) '***
TextBox1.TopLeftCell.Resize(1, UBound(Arr)) = Arr '***
Me.ListBox1.Clear
Me.TextBox1 = ""
Me.ListBox1.Visible = False
Me.TextBox1.Visible = False
' ActiveCell.Offset(0,1).Select
End Sub
|
|