Listbox Cell Selection

[Brian C]

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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Brian C]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >