populating a list box

[Guest]

I am using the row source to populate a list box. Is there a way to populate
the list box using different ranges of a worksheet. eg B63:G71 and B82:G85.

 

[Bob Phillips]

You could set the RowSource property dynamically in code.

When would you want the different ranges?

--

HTH

Bob Phillips

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

I am using the row source to populate a list box. Is there a way to populate
the list box using different ranges of a worksheet. eg B63:G71 and

B82:G85.

 

[Guest]

The information I want to put in the list box is within the range B63 to G271.
Some of the information in this range I don't want shown.
If I hide these rows is there any way of not showing them in the list box?

 

[Bob Phillips]

Not unless you iterate through them and pick out the non-hidden items.

--

HTH

Bob Phillips

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

 

[Guest]

Can you give me an example of how I would do this please?

 

[Bob Phillips]

Something like

ListBox1.Clear
For Each cell In Range("H1:H100")
If Not cell.EntireRow.Hidden Then
Listbox1.Additem cell.Value
End If
Next cell


--

HTH

Bob Phillips

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

 

[Guest]

Where do I write the code Bob?

Something like

ListBox1.Clear
For Each cell In Range("H1:H100")
If Not cell.EntireRow.Hidden Then
Listbox1.Additem cell.Value
End If
Next cell


--

HTH

Bob Phillips

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

 

[Bob Phillips]

That would depend upon where your listbox is, how it is invoked etc.

Can you give more details?

--

HTH

Bob Phillips

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

 

[Guest]

My listbox is on a userform. The row source is b63:g271. It has six columns.
The following code is used to select the same item on the worksheet from the
listbox.

Private Sub ListBox1_Click()
'This is the code for the listbox
'It links the listbox to the sheet

With activeworksheet
range("B63:B271")(UserForm1.ListBox1.ListIndex + 1).Select
End With
End Sub