Inserting SpreadSheet rows into UserForm Listbox?

[logius]

Is it possible to insert groups of rows into a ListBox on a UserForm through VBA? The AddItem method doesn't seem be the right way to go for this.

Any assistance would be greatly appreciated.

----------------------------------------
If you are reading this, then you have read too far...

 

[SkipVought]

Hi,

The ListFillRange can be referenced by Sheet and column range
[tt]
Sheet1!A2:A10
[/tt]
I perfer using Named Ranges and then referening the Range Name in the ListFillRange box.

How can I rename a table as it changes size faq68-1331

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[logius]

There is no ListFillRange property for a UserForm ListBox. The RowSource is the closest thing.

----------------------------------------
If you are reading this, then you have read too far...

 

[SkipVought]

Same thing.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[logius]

The only problem is the RowSource will only allow a single range to be set. I tried using a disjointed NamedRange to simulate what I'll need, but it didn't work (although it didn't give an error the list remained blank).

What I need is a routine to add in lines to the ListBox based on the results of an active search. The goal is to have something similar to the layout of the Search function in the Windows' Start Menu.

----------------------------------------
If you are reading this, then you have read too far...

 

[SkipVought]

AddItem will work. A non-contigulus range referenc will not.

Where is your code that did not work?

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[logius]

ActiveWorkbook.Names.Add Name:="NamedRange", RefersToR1C1:= _
"='Full Data'!R2,'Full Data'!R4,'Full Data'!R6, _
'Full Data'!R10,'Full Data'!R14,'Full Data'!R18, _
'Full Data'!R21"

Form2.ListBox1.RowSource = ActiveWorkbook.Names("NamedRange")


----------------------------------------
If you are reading this, then you have read too far...

 

[SkipVought]

That will not work.

What about the AddItem code that you earlier said did not work?

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[logius]

The row I want to add in is sent to a seperate procedure via a variable value (RwIdx).

Sub SrchResult(RwIdx As Integer)

   frmResults.lbxResult.AddItem (ActiveSheet.Rows(RwIdx))

End Sub

SrchResult is called each time an item in the spreadsheet meets the criteria of the search. I've even tried hardcoding multiple calls to the procedure using various RwIdx values, but I keep getting a Type-Mismatch error on that line.

----------------------------------------
If you are reading this, then you have read too far...

 

[PHV]

I really don't think the AddItem method of an UserForm.ListBox admits a Range object as argument ...
When the cursor is inside the word AddItem in your code feel free to press the F1 key to get more info on this method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

ActiveSheet.Rows(RwIdx) returns a ROW

You want a VALUE.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[SkipVought]

ActiveSheet.Rows(RwIdx) returns a ROW

You need a VALUE.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}