Listbox Cell Selection

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brian C - 27 Mar 2006 23:23 GMT

Hi,

I created a listbox from a range of cells. I need to identify the cell that
contains the value of the item in the listbox that was selected, and copy the
entire row.

I'd appreciate any help in:
1. Identifying the selected item
2. Identifying the corresponding cell

Thanks,

Brian

Reply to this Message

Tom Ogilvy - 28 Mar 2006 02:01 GMT

if it is set with the rowsource

Private Sub Listbox1_Click()
Dim rng as Range, rng2 as Range
set rng = Listbox1.RowSource
msgbox Listbox1.value & " - " & listbox1.ListIndex
set rng1 = rng(listbox1.ListIndex+1)
rng1.EntireRow.copy Worksheets("Sheet2") _
.Cells(rows.count,1).End(xlup)(2)
End Sub

Signature

Regards,
Tom Ogilvy

> Hi,
>
[quoted text clipped - 9 lines]
>
> Brian

Reply to this Message

Brian C - 28 Mar 2006 15:26 GMT

Hi Tom,

Thanks for the quick response. Unfortunately, I didn't use rowsource to add
the items to the listbox, so I got a Type Mismatch error when I ran your
code. Here's the code I used to initialize the listbox. If rowsource is a
better way to add the value, I'd appreciate your help with that:

Private Sub userform_initialize()

Dim AllCells As Range, Cell As Range
Dim myStart As Range
Dim destWB As Workbook
Dim searchltr As String, testltr As String
Dim sourceVal As String
Dim sourceWB As Workbook

Set sourceWB = Workbooks("CustomerData.xls")
Set destWB = Workbooks("ODonnell Sales Model16.xls")

Set sourceRange = destWB.Sheets("Customer Data").Range("b6")

If sourceRange.Value = "" Then
MsgBox "Please enter a Name"
sourceRange.Select
Exit Sub
End If

If Len(sourceRange.Value) >= 1 Then searchltr =
UCase(Left(sourceRange.Value, 1))

sourceWB.Activate
Set myStart = Range("D:D")
x = myStart.End(xlDown).Row - myStart.Row + 1

For a = 2 To x
Set AllCells = Range("d" & a)
Let testltr = UCase(Left(AllCells.Value, 1))
If searchltr = testltr Then
Me.ListBox1.AddItem AllCells.Value
End If
Next a

' Show the UserForm
UserForm1.Show
End Sub

> if it is set with the rowsource
>
[quoted text clipped - 22 lines]
> >
> > Brian

Reply to this Message

Tom Ogilvy - 28 Mar 2006 19:56 GMT

there are faster ways, but you can use your same code

Private Sub Listbox1_Click()

Dim AllCells As Range, Cell As Range
Dim myStart As Range
Dim destWB As Workbook
Dim searchltr As String, testltr As String
Dim sourceVal As String
Dim sourceWB As Workbook
Dim i as Long
Dim rng as Range

if me.Listbox1.ListIndex = -1 then exit sub
Set sourceWB = Workbooks("CustomerData.xls")
Set destWB = Workbooks("ODonnell Sales Model16.xls")

Set sourceRange = destWB.Sheets("Customer Data").Range("b6")

If Len(sourceRange.Value) >= 1 Then searchltr = _
UCase(Left(sourceRange.Value, 1))

sourceWB.Activate
Set myStart = Range("D:D")
x = myStart.End(xlDown).Row - myStart.Row + 1

For a = 2 To x
Set AllCells = Range("d" & a)
If AllCells.Value = me.ListBox1.Value then
set rng = AllCells
exit for
End If
Next a
if not rng is nothing then
application.goto rng, True
else
' you should never get this message
msgbox "Not found
End if
End Sub

Signature

Regards,
Tom Ogilvy

> Hi Tom,
>
[quoted text clipped - 70 lines]
> > >
> > > Brian

Reply to this Message