listbox

[Guest]

Private Sub UserForm_Initialize()
Dim cell As Range
With Me.ListBox1
.Clear
For Each cell In ActiveSheet.Range("B6:B27")
If Not cell.EntireRow.Hidden Then
.AddItem cell.Value
.List(.ListCount - 1, 1) = cell.Offset(0, 1)
.List(.ListCount - 1, 2) = cell.Offset(0, 2)
.List(.ListCount - 1, 3) = cell.Offset(0, 3)
.List(.ListCount - 1, 4) = cell.Offset(0, 4)
.List(.ListCount - 1, 5) = cell.Offset(0, 5)
End If
Next cell
End With
End Sub

I got this code to hide rows in a listbox. The problem is when I select the
rows in the listbox I want to be able to select the corresponding item on the
worksheet. Because of the code this doesnt work. Here is the code I use to
link the listbox and worksheet.

Private Sub ListBox1_Click()
'This is the code for the listbox
'It links the listbox to the sheet
With activeworksheet
range("B63:B66", "B74:B76")(UserForm1.ListBox1.ListIndex + 1).Select
End With
End Sub

 

[Bob Phillips]

That is because the listbox index is not one-for-one aligned to the range.
You will have to match the selected value against the range, and work out
its index.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

How do I do that Bob??

That is because the listbox index is not one-for-one aligned to the range.
You will have to match the selected value against the range, and work out
its index.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)