PDA

View Full Version : Solved: Uploading a ComboBox from a Column in Excel



chamdan
04-25-2012, 05:50 PM
Hi,

I created a Userform where a comboBox is located and have inserted in the property of the ComboBox in RowSource = [range name] where range name is AD3:AD2000 and have given the name of the range as "IssuesQ" now the Rowsource contains:
RowSource=IssuesQ
But when I load the form and click on the ComboBox it list all the names I want however it inserts even the blank row cells available in the column. How can I make it work to remove all balnk lines from the ComboBox?

Thanks for your help

Chuck

Tinbendr
04-26-2012, 07:07 AM
Remove the rowsource, then add this code to the Initialize event.

Private Sub UserForm_Initialize()
Dim aCell As Range
With Me.ComboBox1
For Each aCell In Range("IssuesQ")
If aCell <> "" Then
.AddItem aCell.Value
End If
Next
End With

End Sub

snb
04-26-2012, 07:23 AM
Do not use 'rowsource' nor 'additem', but:


Private Sub UserForm_Initialize()


combobox1.list=filter([transpose(if(AD3:AD2000="","~",AD3:AD2000))],"~",false)
End Sub

the use of a named range in this case is redundant, but if you stick to it you could also use:


Private Sub UserForm_Initialize()


combobox1.list=filter([transpose(if(IssuesQ="","~",IssuesQ))],"~",false)
End Sub

chamdan
04-26-2012, 03:25 PM
Thank you for your help! and as SnB said, not to use the Rowsource I fully agree as you may fall into a problem that occured with me and surely with may others. it gives you an error such as: Error 70 denied access! or something similar not sure what was the message I got but remember the error number and the denied message..


Thank you!

Cheers!

Chuck