CODEWITHSUNDEEP
vbaexcelcombobox
user21354
user21354

Reputation: 21

Filling VBA userform's combobox with table column plus an additional option

I am developing an application, in excel with VBA forms. in one form I have a combobox to that let user select customer name, the rowsource of this combobox is a named range (name column of customers table). Everything working fine but I need to add 1 or more additional items in the combobox that not exist in the table column. For example I need to add "All" item in the cobmobox so user can select a particular customer name or All. at other place I wan't to add "Other" item in combobox with same rowsource so if the customer is new user can select Other and then type name in textbox.

I tried following code to add an item 

Private Sub UserForm_Activate()
With Me.testCombo
.AddItem "All"
End With
End Sub

but i got error Run-time error '70' permission denied

if i remove rowsource property from the combobox then the above code work but only one item "All" display.

Note: I don't want to add "All" and "Other" in customer table, this could be easy solution but will cause other problem.

Upvotes: 1

Views: 5977

Answers (3)

user21354
user21354

Reputation: 21

Thank you everyone for helping, the main problem was permission as A.S.H said if Rowsource is set then cannot add any item in the ComboBox. So I delete the RowSource from the properties in form. and wrote following code and it seems everything working fine. I hope my codes are good enough and simple.

Private Sub fillComboBox()
        Dim comboData As Range

        With Me.CWR_CustName
            ' first option of comobobox will be All
            .AddItem "All"

            For RW_Cust = 1 To Range("tblCust").Rows.Count

                ' add each customer name from customer table name column
                .AddItem (Range("tblCust[Name]")(RW_Cust))

            Next RW_Cust

        End With
End Sub

Upvotes: 0

Nathan_Sav
Nathan_Sav

Reputation: 8531

Something like this could do what you need

Dim a() As Variant
Dim b() As String
Dim s As String

a = Application.Transpose(Range("a1:a5").Value)
s = "Please select;" & Join(a, ";")
Erase a
b = Split(s, ";")

Me.ComboBox1.List = b

Upvotes: 0

Vityata
Vityata

Reputation: 43585

Try like this:

Private Sub UserForm_Activate()

    Dim rowValue        As Variant
    Dim lngCount        As Long
    Dim myCell          As Range
    Dim varCombo()      As Variant

    With Me.ComboBox1

        ReDim varCombo(Me.ComboBox1.ListCount)          

        For Each myCell In Range(.RowSource)
            varCombo(lngCount) = myCell.value
            lngCount = lngCount + 1
        Next myCell

        .RowSource = ""

        For lngCount = LBound(varCombo) To UBound(varCombo) - 1
            .AddItem CStr(varCombo(lngCount))
        Next lngCount

        .AddItem "All"
        .AddItem "Nothing"

    End With

End Sub

As mentioned in the comments, by A.S.H., you should unset the .RowSource property. However, you do not lose it, if you run the code twice, it would be the same. In my code I use UBound(varCombo) - 1, because I use lngCount=lngCount+1 on the last looping over the cell.

Upvotes: 1

Related Questions