set the scrollbar position of a listbox 1

[amigofd]

Hello,

i have two listboxes, the first with a temporary clientname, the second with the existing clients. I you select a record from the the first list, i want to scroll the second to the clients starting with the first letter of the temporary clientname.

For example:
* 1st listbox: select "Testclient"
* 2nd listbox is positioned at the clients:
Test 1
Test 2
Test 3

Because the second list has over 2000 records it's pretty difficult to scroll by hand to the correct position ...

Any ideas for this ?

Greetins,
Frank

 

[littlewoman]

I was just wondering reading your post why you don't fill the second listbox with the filtered data as soon as a choice is made in the first listbox?

By far I'm no expert but this is how I would do it i.e.:

Private Sub Listbox1_Change
'GET FIRST LETTER
character = left(listbox1.value,1)

'CLEAR DATA FROM LISTBOX2
listbox2.clear

'GET CLIENTS
For cnt=1 to [Total No of Clients]
if left([Client],1)=character then _
listbox2.additem [Client]
next cnt
End sub

Maybe it helps.
have fun

 

[GVF]

I think Frank wants to simply avoid tedious scrolling through the second list box. I assume that Listbox2 is alphabetized...

Private Sub ListBox1_Change()
With UserForm1
Counter = 0
For Each LName In .ListBox2.List
If Left(.ListBox1.Value, 1) = Left(LName, 1) Then
.ListBox2.Value = .ListBox2.List(Counter)
Exit Sub
End If
Counter = Counter + 1
Next LName
End With
End Sub

Greg

 

[SkipVought]

Hi,

It's pretty simple using the Advanced Filter - Unique Values and the OFFSET spreadsheet function in Insert/Name Define to name the filtered range and use that name as the Row Source for the second combobox.

The only code is picking off the first character of combobox1 selection and using that value in the Advanced Filter Criteria.

Skip,
[sub]
[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen![/sub]