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

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

--
Regards,
Tom Ogilvy



"Brian C" wrote in message
...
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

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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy



"Brian C" wrote in message
...
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

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

--
Regards,
Tom Ogilvy


"Brian C" wrote:

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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy



"Brian C" wrote in message
...
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