Range to combobox- forget the blanks?

  • Thread starter Thread starter Chris A
  • Start date Start date
C

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
 
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
 
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.
 
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
 
Back
Top