list box location

ttowncorp

Board Regular
Joined
Feb 2, 2015
Messages
183
Office Version
  1. 365
Platform
  1. Windows
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
'
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
I looked at your question and I do not understand what bu is.

And it may help to just explain what you want the script to do.
It appears as if it's hiding Controls for one thing.
 
Upvote 0
I worked with just a little bit of your code. And this worked for me:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Modified  9/22/2018  3:15:05 AM  EDT
If Target.CountLarge > 1 Then Exit Sub
Select Case Target.Column
Case 5, 6, 7, 8
If Target.Row > 1 Then
'bu = True
    With Me.TextBox1
        .Top = Target.Top: .Left = Target.Left: .Text = Target.Value: .Activate
    End With
End If
End Select
End Sub
 
Last edited:
Upvote 0
thanks for looking at this. but yes you are correct about it will only show information in that cell once I typing if only I have it in my target list. it was to prevent people to only use the specific wording that I have added. I type the letter "F" and any word or sentence starting with the letter "F" will pop up on the right hand side of the cell your typing. problem is sometimes it won't let you select anything it finds and i'll have to click a different cell and then go back to that cell again for it to let me select anything. it almost like if locks up and I can't figure it out at all. I have notice if I remove the following
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


It wont lock up but I lose the option to double click anything that populates.
 
Last edited:
Upvote 0
I really do not have a answer for you. Maybe someone else here can help.

And I asked what is bu and you never answered that question.
 
Upvote 0
I'm assuming "BU" is being used as a reference to the location of my list box.
 
Upvote 0
To much here for me to understand. Maybe someone else will be able to help you.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top