List box to a Cell

[Hazel]

Hi

I have a listbox with 20 columns per row, what code would I need to select a
cell, every time I click on members name in the list box I would like it to
choose the activeCell on the sheet ("Members") the cell would always be in
Column "I" i.e.
Lb1.List(, 8) - I can then find and change the date easily - with 600
members it is so time consuming scrolling down the list.

 

[Joel]

Here is the code. I assumed there was only one list box on the page. the
code goes on the VBA worksheet where the list box is located. Not a module,
but something like sheet3.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Set listaddress = ActiveSheet.ListObjects(1).Range

Set isect = Application.Intersect(Target, listaddress)

If Not isect Is Nothing Then

With Sheets("Members")
.Activate
LastRow = .Cells(Rows.Count, "I").End(xlUp).Row
Set ColIRange = .Range(.Cells(1, "I"), .Cells(LastRow, "I"))
End With

Set c = ColIRange.Find _
(what:=Target.Value, LookIn:=xlValues)

If Not c Is Nothing Then
c.Select
Else
MsgBox ("Cannot find : " & Target.Value)
End If
End If

End Sub

 

[Hazel]

Hi Joel

Thanks for the reply - I should really have given more info, although I have
learnt something and will use your code in another small program I'm using in
Excel.
The list box is on a Userform and what I wanted to do was select say J.
Bloggs in Lb1 and on selection - in the "Members" sheet it would select the
Cell that corresponds to Lb1.List(, 8) and make it the Active Cell -- Ican
then enter the date from a Calendar by just clicking on the Selected Date.
Have tried the Change Event in Lb1 however I cannot get it to Select the
Active Cell.

Any further help much appreciated.

 

[Joel]

Most people use the list method on the worksheet, few us the userforms. did
you create the userform in the VBA window by using the menu option Insert
Userform?

If so you have to close the userform and then read the contents of the list
box. Next perform the search of the worksheet column I like I did in the
code I gave you.

userform1.hide
select_text = userform1.listbox1.text

With Sheets("Members")
.Activate
LastRow = .Cells(Rows.Count, "I").End(xlUp).Row
Set ColIRange = .Range(.Cells(1, "I"), .Cells(LastRow, "I"))
End With

Set c = ColIRange.Find _
(what:=select_text, LookIn:=xlValues)

If Not c Is Nothing Then
c.Select
Else
MsgBox ("Cannot find : " & select_text)
End If