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
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.
Bookmarks