Dynamic combo row source - works only if active source worksheet

**fredpox** · 03-20-2010, 08: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

Code:

Sub OpenForm()
'limits combo row source by counting rows with data in column C of DATI worksheet
Dim countnonblank As Integer, myRange As Range
Set myRange = Worksheets("DATI").Columns("C:C")
countnonblank = Application.WorksheetFunction.CountA(myRange)
'MsgBox "The number of rows is " & countnonblank
Dim Rng As Range
Set Rng = Worksheets("DATI").Range("C2:C" & countnonblank)
FormOffertaArredi.sceltaArredi.RowSource = Rng.Address

FormOffertaArredi.Show
End Sub

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, 08:40 AM

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

Code:

Dim rComboRng As Range
With Worksheets("DATI")
Set rComboRng = .Range(.Cells(1, 3), .Cells(.Rows.Count, 3).End(xlUp))
End With
FormOffertaArredi.sceltaArredi.Value = rComboRng.Value

**fredpox** · 03-20-2010, 09: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!

Code:

Sub OpenForm()
'limits combo row source by counting rows with data in column C of DATI worksheet
Dim rComboRng As Range
With Worksheets("DATI")
Set rComboRng = .Range(.Cells(2, 3), .Cells(.Rows.Count, 3).End(xlUp))
End With
FormOffertaArredi.sceltaArredi.RowSource = "DATI!" & rComboRng.Address
FormOffertaArredi.Show
End Sub

Thanks again for your help!

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

Sorry,codeshould be

Code:

Dim rComboRng As Range
With Worksheets("DATI")
Set rComboRng = .Range(.Cells(1, 3), .Cells(.Rows.Count, 3).End(xlUp))
End With
FormOffertaArredi.sceltaArredi.List = rComboRng.Value

**fredpox** · 03-20-2010, 01: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, 03:16 PM

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

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

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

Code:

FormOffertaArredi.sceltaArredi.RowSource = "DATI!" & rComboRng.Address

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, 07:52 AM

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

Code:

FormOffertaArredi.sceltaArredi.RowSource = rComboRng.Address(external:=True)

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

**fredpox** · 03-21-2010, 08: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.