ComboBox

[Paleo]

I wanna populate a ComboBox programmatically, adding items to be displayed and results to be sent.

Something like this:

Dim cb As ComboBox
Dim Pag(10) As String
Pag() = {"RS","SC","PR","SP","RJ","DF","MG","BA","MT","PE","AM"}
Set cb = cboEstados
cb.Clear
For i = 0 To 10
     cb.AddItem Pag(i)
Next

This only adds the labels (RS, SC, PR, ...) but I want to tell the combobox when I choose RS to write in the linked cell "Rio Grande do Sul".

Any clues?

[Zack Barresse]

Well, I'd suggest one of two things:

1) Use the real names instead of initials in your array.

2) Create a 3-D array with the associated values in the second column of the array, then perform a lookup on the array when the initials are selected from the combobox.

[Paleo]

Hi Zack,

I like the second suggestion best because I am populating dynamically that array and I want to show the full text only when someone chooses that initials. Would you suggest me to create a list with two columns and use it as the ListFillRange for the ComboBox or anything better?

[mdmackillop]

or
Create a combobox with 2 columns (ColumnCount = 2, BoundColumn = 1)
Col 1 = FullName, Width = 0
Col 2 = Abbrev., Width = 20 (or whatever)

[Paleo]

Hi MD this is exactly what I want to do, but just dont know how to programmatically. How to add the column 2?

[Zack Barresse]

Well, to me I guess it would depend on who was going to be using this. If a user was going to be doing most of the work on this and I didn't want them to change anything on it, I'd leave most everything in the code. But if it is something that will be changed often or be used solely by the developer, I may choose what you are proposing.

An example:

I create a Combobox (ComboBox1) and a Textbox (TextBox1) both on Sheet1.
In the Combobox properties, I adjust the ColumnWidth to 2 and set the ListFillRange to my two column list.
In Sheet1 module I put this code ..

Option Explicit

Private Sub ComboBox1_Change()
    With Me.ComboBox1
        Me.TextBox1.Value = .List(.ListIndex, 1)
        End With
End Sub

[Zack Barresse]

To set the ColumnWidth enter Design Mode, right click the Object and select Properties.

[Paleo]

Hi Zack,

well I need to create a ComboBox and populate it by code. Ten Titles with text about then, i.e. RS, Rio Grande do Sul. When the user chooses a title he gets the text but the title and the text are not at the spreadsheet, I am getting then from the web, so there are very changeable.

[Zack Barresse]

Hmm. Can you post what you have so far? Is this an Excel WebQuery? How do you get the data? And where does it go into?

[Paleo]

Hi Zack,

its a Web query, I do a search over the web and put the ten results on a variable called Pag(0 to 10) using the AddItem (I just put the pages title in it), but I need to put the pages url too. Its a file I am working to submit at the knowledge base. I do a search over google and put the result in a combobox.

[Paleo]

Should I be postting at the KB forums or can it be here too?

[Zack Barresse]

Can you upload a sample file?

And this is ok here.

[mdmackillop]

Paleo,
Adapted from the Help file

Private Sub UserForm_Initialize()
    Dim i As Single
     'The combobox contains 3 data columns
    ComboBox1.ColumnCount = 3
'Load integer values into first column of MyArray
    For i = 0 To 5
        MyArray(i, 0) = i
    Next i
    'Load columns 2 and three of MyArray
    MyArray(0, 1) = "Zero"
    MyArray(1, 1) = "One"
    MyArray(2, 1) = "Two"
    MyArray(3, 1) = "Three"
    MyArray(4, 1) = "Four"
    MyArray(5, 1) = "Five"
    MyArray(0, 2) = "Zero"
    MyArray(1, 2) = "Un ou Une"
    MyArray(2, 2) = "Deux"
    MyArray(3, 2) = "Trois"
    MyArray(4, 2) = "Quatre"
    MyArray(5, 2) = "Cinq"
    'Load data into ComboBox1
    ComboBox1.List() = MyArray
End Sub

[johnske]

Hi Paleo,

Look at what MD's done above and here for the basic principles, then look here and here for a bit more info.

Regards,
John


EDIT: (Populating Combo & List boxes is almost identical)

[Paleo]

Hi guys,

thanks for the help, this was all I needed. I am submiting my entry now. Its a search over google by excel, exposing the results in the plan.