I did not write this code, I had a co-worker long time ago write it for me, but if I understand correctly under .Top = Target.Top + 30: .Left = Target.Left + 143: .Clear , I this the location where my text box pops up on my sheet? so if I change these numbers I can have it moved around some bit. but what I don't understand is the function of this one and why target only column 6 when I want the function to work in columns in 5, 6, 7, and 9 as well. cl = Target.Column + 6: bu = False
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Select Case Target.Column
Case 5, 6, 7, 9
If Target.Row > 1 Then
bu = True
With Me.TextBox1
.Top = Target.Top: .Left = Target.Left: .Text = Target.Value: .Activate
End With
With Me.ListBox1
.Top = Target.Top + 30: .Left = Target.Left + 143: .Clear
End With
cl = Target.Column + 6: bu = False
Me.TextBox1.Visible = True: Me.ListBox1.Visible = True
End If
Case Else
Me.TextBox1.Visible = False: Me.ListBox1.Visible = False
End Select
End Sub
Private Sub TextBox1_Change()
Dim x, i As Long, txt As String, lt As Long, s As String
If Len(TextBox1.Text) = 0 Or bu Then Exit Sub
txt = TextBox1.Text: lt = Len(TextBox1.Text)
'x = Columns(cl).SpecialCells(2).Value
x = Range(Cells(2, cl), Cells(Rows.Count, cl).End(xlUp)).Value
For i = 1 To UBound(x, 1) ' ïîèñê ïî ïåðâûì áóêâàì
If txt = Mid(x(i, 1), 1, lt) Then s = s & x(i, 1) & "~"
Next i
ListBox1.List = Split(s, "~")
End Sub
Private Sub ListBox1_Click()
If ListBox1.ListIndex = -1 Then Exit Sub
bu = True
ActiveCell.Value = ListBox1.Value
Me.TextBox1.Text = ListBox1.Value
bu = False
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
If ListBox1.ListIndex = -1 Then Exit Sub
bu = True
ActiveCell.Value = ListBox1.Value
Me.TextBox1.Visible = False
Me.ListBox1.Visible = False
bu = False
End Sub
'Sub tt()
'Application.EnableEvents = True
'End Sub
'
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Select Case Target.Column
Case 5, 6, 7, 9
If Target.Row > 1 Then
bu = True
With Me.TextBox1
.Top = Target.Top: .Left = Target.Left: .Text = Target.Value: .Activate
End With
With Me.ListBox1
.Top = Target.Top + 30: .Left = Target.Left + 143: .Clear
End With
cl = Target.Column + 6: bu = False
Me.TextBox1.Visible = True: Me.ListBox1.Visible = True
End If
Case Else
Me.TextBox1.Visible = False: Me.ListBox1.Visible = False
End Select
End Sub
Private Sub TextBox1_Change()
Dim x, i As Long, txt As String, lt As Long, s As String
If Len(TextBox1.Text) = 0 Or bu Then Exit Sub
txt = TextBox1.Text: lt = Len(TextBox1.Text)
'x = Columns(cl).SpecialCells(2).Value
x = Range(Cells(2, cl), Cells(Rows.Count, cl).End(xlUp)).Value
For i = 1 To UBound(x, 1) ' ïîèñê ïî ïåðâûì áóêâàì
If txt = Mid(x(i, 1), 1, lt) Then s = s & x(i, 1) & "~"
Next i
ListBox1.List = Split(s, "~")
End Sub
Private Sub ListBox1_Click()
If ListBox1.ListIndex = -1 Then Exit Sub
bu = True
ActiveCell.Value = ListBox1.Value
Me.TextBox1.Text = ListBox1.Value
bu = False
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
If ListBox1.ListIndex = -1 Then Exit Sub
bu = True
ActiveCell.Value = ListBox1.Value
Me.TextBox1.Visible = False
Me.ListBox1.Visible = False
bu = False
End Sub
'Sub tt()
'Application.EnableEvents = True
'End Sub
'