PDA

View Full Version : Solved: ComboBox



Paleo
02-10-2005, 01:28 PM
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?:banghead:

Zack Barresse
02-10-2005, 01:34 PM
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
02-10-2005, 01:40 PM
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
02-10-2005, 01:45 PM
or
Create a combobox with 2 columns (ColumnCount = 2, BoundColumn = 1)
Col 1 = FullName, Width = 0
Col 2 = Abbrev., Width = 20 (or whatever)

Paleo
02-10-2005, 01:48 PM
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
02-10-2005, 01:48 PM
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
02-10-2005, 01:49 PM
To set the ColumnWidth enter Design Mode, right click the Object and select Properties.

Paleo
02-10-2005, 01:51 PM
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
02-10-2005, 02:05 PM
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
02-10-2005, 02:10 PM
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
02-10-2005, 02:10 PM
Should I be postting at the KB forums or can it be here too?

Zack Barresse
02-10-2005, 02:14 PM
Can you upload a sample file?

And this is ok here. :)

mdmackillop
02-10-2005, 02:27 PM
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
02-10-2005, 03:54 PM
Hi Paleo,

Look at what MD's done above and here (http://www.vbaexpress.com/kb/getarticle.php?kb_id=233) for the basic principles, then look here (http://www.geocities.com/johnske100/Popul8_3ColListBox_1.html)and here (http://www.geocities.com/johnske100/Popul8_3ColListBox_2.html)for a bit more info.

Regards,
John


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

Paleo
02-11-2005, 08:33 PM
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.