|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
菜鸟求助: 由于需求急,还没来得及学习到数组,字典,窗体控件这些,参考论坛分享的案例琢磨着改了下,存在下列问题,烦请路过坛友指点:
1.第一次点击设置了提示框的单元格时页面会刷新闪一下,能否改善?
2.如红色代码,如果是跨工作簿数据引用代码怎么实现?
3.如附件,在”A"表有提示框的单元格输入后选择提示项(即”数据源“表C列的辅助搜索标签),如何把选择的提示项(即搜索标签)所在行的数据对应写入到"A"中。
4.最底下的红色代码 还不太会改,有时“数据源”表改动后会出错。
5.使用了输入提示框后 不能ctrl+z撤销命令,有方法解决吗?
问题太多,谢谢耐心看完,感谢!
代码附上:
Public Arr, col%, d
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim i&
If KeyCode = 13 Then ActiveCell = TextBox1.Text: Me.TextBox1.Visible = False: Me.ListBox1.Visible = False: Exit Sub
Set d = CreateObject("Scripting.Dictionary")
Me.ListBox1.Clear
Call yy(col)
If d.Count > 0 Then Me.ListBox1.List = d.keys
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then GoTo 100: Exit Sub
If (Target.Column <> 2) Or Target.Row < 8 Then GoTo 100: Exit Sub
Sheets("数据源“).Unprotect
col = Target.Column
Arr = Sheets("数据源“).Range("a1").CurrentRegion '外部数据源表怎么引用?
With Me.TextBox1
.Visible = True
.Text = ""
.Top = Target.Top
.Left = Target.Left
.Width = Target.Width
.Height = Target.Height + 5
.Activate
End With
With Me.ListBox1
.Visible = True
.Top = Target.Top
.Left = Target.Left + Target.Width
.Height = Target.Height * 10
End With
Exit Sub
100:
Me.TextBox1 = ""
Me.ListBox1.Visible = False
Me.TextBox1.Visible = False
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
ActiveCell = ListBox1.Value
Me.ListBox1.Clear
Me.TextBox1 = ""
Me.ListBox1.Visible = False
Me.TextBox1.Visible = False
End Sub
Sub yy(col) '参数不知道怎么设置!
Dim i&, c%
If col = 2 Then c = 3
If TextBox1.Text = "" Then
For i = 2 To UBound(Arr)
If Arr(i, c) <> "" Then d(Arr(i, c)) = ""
Next
Else
For i = 2 To UBound(Arr)
If InStr(Arr(i, c), Me.TextBox1.Text) Then
If Arr(i, c) <> "" Then d(Arr(i, c)) = ""
End If
Next
End If
End Sub
|
|