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.

Silly question, but you are trying to do this once the UserForm is loaded? Where is this code placed? And did you clear the ComboBox before trying to add the new tariffs? — R3uK, Nov 12 '15 at 10:01
When the workbook is opened the userform initializes immediately with several comboboxs on (1 for each sales Channel). When they select there sales channel a seperate userform appears propmting for a password. When they hit "submit password" it then inputs the name on the sales channel onto the "Tariff Matrix" page which then filters the tariffs only available to that channel. What i want is for the submit command button to not only filter but then populate the combobox(s) with only the visible tariffs (offset i believe is the only method for this). Also i haven't cleared the combobox first. — Peter Mogford, Nov 12 '15 at 10:09
What does the loop `For Each TLoc In ws.Range("Tariffs")` do? I assume there is only one range named "Tariffs" in the workbook? — eirikdaude, Nov 12 '15 at 10:43
I actually think i could remove the .Range("Tariffs") part as i have already created a variable for this. You are correct in your assuption. The range "Tariffs" is the tariffs within the autofilter table. — Peter Mogford, Nov 12 '15 at 10:52
The RowSources have been left blank so they are currently empty. — Peter Mogford, Nov 12 '15 at 10:54
Hmm, is this post about how to populate a listbox applicable to your situation? http://www.ozgrid.com/forum/showthread.php?t=62504&page=2&p=324557#post324557 Oh, and since me and R3uK aren't the ones who made the question, we do not get notifications when you reply to us, unless you address the comments with either @ eirikdaude or @ R3uK (without the spaces). Doing that will increase the chance that either of us will come back to have a look at your problem :) — eirikdaude, Nov 12 '15 at 11:18
what is`MobilePricing.Tariff1`? I don't think it is the name of the combobox. I also agree the RowSource is filled when that error pops up. Is this the UserForm initialize code? — Davesexcel, Nov 12 '15 at 12:54
@Davesexcel "MobilePricing" is the Userform that ComboBox ("Tariff1") sits on — Peter Mogford, Nov 12 '15 at 12:57
@R3uK All the RowSources are blank although the one i was referencing had a space in it. I have now removed this and the code runs but it adds all the items in the list. This would be good but i only want to be able to see the visible tariffs left after the autofilter. Am i right in thinking that my only option to do this is using the offset method? — Peter Mogford, Nov 12 '15 at 15:13

R3uK · Accepted Answer · 2015-11-12T15:59:01.690

2

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

edited Nov 12 '15 at 15:59

answered Nov 12 '15 at 15:18

R3uK

14,417
7
43
77

1

this is great thank you very much... Working perfectly. If i wanted to extend this and create another variable so this code applies to all of the comboboxs (10 in Total) then how would i do that. – Peter Mogford Nov 12 '15 at 15:32
Sorry forgot to mention all the combobox's are labelled the same (Tariff1,Tariff2... Tariff10) @R3uK – Peter Mogford Nov 12 '15 at 15:42
Sorry I dont understand. I thought i would just have to create a variable i.e **Dim Combos As Control** and then **Set Combos = Tariff1 etc...** @R3UK – Peter Mogford Nov 12 '15 at 15:56

Creating .additem for combobox

1 Answers1