Dynamic combo row source - works only if active source worksheet

**fredpox** · 03-20-2010, 07:21 AM

Hi there,
I'm here again for another topic which is quite puzzling me up.
I want to define a row source for a userform's combo but the rows may vary according to new data entered in a given worksheet.

My source worksheet is named DATI and the cell range that I want to define is in column C:C.
At first I defined in the userform properties for the combo a row source value like this : DATI!C2:C200 but many rows are still empty and I don't want that.
Then I defined a function which assigns the row source via code like this

Please Login or Register  to view this content.

this code actually works but only if I launch the function with DATI worksheet as the active worksheet..as soon as I launch the function in another worksheet (as I want it to) the function gives the combo a row source of column C referred to the active worksheet

How can I fix that to point always to my DATI worksheet?

Thanks in advance for your time
P.

**royUK** · 03-20-2010, 07:40 AM

It's an over cpmplicated way to do it. Try

Please Login or Register  to view this content.

**fredpox** · 03-20-2010, 08:18 AM

Hi royUK,
thanks for your fast reply

I had a few issues when trying your code and the range was once again referred to an unreferenced worksheet.
I change it like this and it works great!

Please Login or Register  to view this content.

Thanks again for your help!

**royUK** · 03-20-2010, 10:22 AM

Sorry,codeshould be

Please Login or Register  to view this content.

**fredpox** · 03-20-2010, 12:21 PM

thanks again royUK! now the code works fine.
Just a 'semantic' question: What the .list does exactly here to include also the worksheet selected?

thank you for all of your time
P.

**royUK** · 03-20-2010, 02:16 PM

It doesn't include the worksheet,the rComboRng is set to the DATI worksheet

**fredpox** · 03-21-2010, 06:29 AM

right but..maybe I wasn't much clear in my question
when I wrote this with .RowSource property

Please Login or Register  to view this content.

if I didn't insert the "DATI!" string, the range would have had a reference to every possible worksheet.
Your code with .List instead retrieved exactly the range you set with the With clause.
My question is, what's the difference between .RowSource and .List as a combo property?

thanks again for your patience
P.

**royUK** · 03-21-2010, 06:52 AM

Try adding external=True,tolet Excelknow that the RowSource is on a different sheet

Please Login or Register  to view this content.

This will show the difference. Rowsource needs the full address,whereas Listuses the Value property of a Range

**fredpox** · 03-21-2010, 07:16 AM

Thanks royUK, that finally makes sense,
I didn't know about that difference but as soon as you posted your code with the .List property something told me that it may be very useful for the future.
thank you for helping me figuring it out. :-)

have a nice day!
P.