Creating .additem for combobox

Question

I have got a list of tariffs that i have set up on an autofilter so that when a specific sales channel is selected and password is correct it shows only the tariffs available to that channel.

My problem is that I cant seem to figure out how to get the command button to also populate the combobox.

my .additem code below keeps returning a

"Permission Denied" error

Dim TLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("Tariff Matrix")
Set TLoc = Range("Tariffs")

For Each TLoc In ws.Range("Tariffs")
    With MobilePricing.Tariff1
        .AddItem TLoc.Value
    End With
Next TLoc

Any assistance will be greatly appreciated.

Answer 1

First you need to check the RowSource of your ComboBox, if it's not empty, empty it.

Then as you want to have only the visible cells (after the autofiler); you need to use Range("Tariffs").SpecialCells(xlCellTypeVisible).

Here is your amended code :

Dim TLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("Tariff Matrix")
Set TLoc = Range("Tariffs")

For Each TLoc In ws.Range("Tariffs").SpecialCells(xlCellTypeVisible).Cells
    With MobilePricing.Tariff1
        .AddItem TLoc.Value
    End With
Next TLoc

To loop on your UserForm Controls, use something like this :

Dim Ctrl As Control

For Each Ctrl In Me.Controls
    If TypeName(Ctrl) <> "ComboBox" Then 
    Else
        MsgBox Ctrl.Object.Name
        'Your code for one combobox (everyone will be referenced as Ctrl)
    End If
Next Ctrl