Range to combobox- forget the blanks?

[Chris A]

I saw a post not long ago showing how to populate a combobox from vba, using
a range of cells but ignoring the blanks. Excellent just what i needed,
However, i can't get mine working right, I get permission denied on my other
userform which i use to call the next, using the userform.show at the end of
my sub. This only happens when I add a loop on the other form

Here's what I saw.. I wonder if somebody could explain the for each line. Or
post another solution that may help me solve my problem TIA
Chris

Private Sub Userform_Initialize()
Dim sh as Worksheet
Dim cell as Range
set sh = Worksheet("sheet1")

for each cell in sh.range(sh.cells(2,5),sh.cells(rows.count,5).End(xlup))
if not isempty(cell) then
combobox1.AddItem cell
end if
Next

End sub

 

[Chris A]

OK, so i pondered and looked, came up with this, great for the first
combobox on the form but i have 12 and whenever i try to add some sort of
looping through the comboxes to fill the same way I get my userform.show
giving permission denied. Am i missing the point?

Private Sub Fill()
Dim Tx As String
For rwIndex = 25 To 35

With Worksheets("Sheet1").Cells(rwIndex, 61)
If .Value = "" Then
Else
Tx = .Value
ComboBox1.AddItem Tx
End If
End With

Next rwIndex
End Sub


I feel this should be a little more referenced or maybe in the wrong place.

I'm learning lots, most is good, but BOY what a learning curve.

Thanks for looking.
Chris

 

[Tom Ogilvy]

No idea what you problem is, or where this code is located, but try

Private Sub Fill()
Dim Tx As String
For rwIndex = 25 To 35

With Worksheets("Sheet1").Cells(rwIndex, 61)
If .Value = "" Then
Else
Tx = .Value
Userform1.ComboBox1.AddItem Tx
End If
End With

Next rwIndex
End Sub

change userform1 to represent the userform name of the userform that holds
the combobox.

 

[Chris A]

Cheers Tom, It appears that i'm a dimbat, I forgot to remove the old
references from the rawsource property, it's amazing what you can miss!?
Mind you having several user forms it would be wise to add a more difinitive
reference.
thanks again.
Chris