multicolumn combobox range

[dickep]

I have a spreadsheet that has some data that I need in a combobox. Now, the problem is that it is multicolumn data with the first column the "Tag" data I need but the other column, which may or may not be contiguous, has the data I need displayed in the combobox. This is being done dynamically.

I am new to VBA and really don't understand comboboxes at all so any info would be helpful.

I would like to understand if the above is even possible . Then I need to know if I can relate the displayed/selected item in the combobox to the "tag" for processing later.

Thanks
E

[Bob Phillips]

This isn't clear, but I am reading it that you want to get data from different places and load it in a combo row

[vba]

ary = [{"Bob","M", 123;"Lynne","F",898;"Amy","F",543}]

With ListBox1
.AddItem "tag name"
.List(.ListCount - 1, 1) = "some value"
.List(.ListCount - 1, 1) = "some other value"
End With
[/vba]

[dickep]

Thanks for this but not sure if it will work.

Here is what I guess you might like for a better explanation

The data to go into the combobox is in AA9 to AJ18. AA9:AA18 is the "Tag" data (I did this statically using option buttons but don't like that approach). Now the data I want to be displayed for selection in the box is contained in column AJ9:AJ18

Also, this data is name POS1 (AA9:AJ18).

Last, I am confused about listbox and combobox. Do you treat them the same? Is there a website that explains the "With...." data that CAN be used/assigned for them?

Thanks again
E

[Norie]

Can't you just set the RowSource to AJ9:AJ18?

Or is there more to it?

[dickep]

Yes, I can set the rowsource to that, however, then the first column shows and I want other than that (usually the last).

So, still trying to understand these.

Thanks again
E

[Bob Phillips]

Didn't Nories example use the last column?

[dickep]

As I said, I do not understand ComboBox stuff. So, the example I got did not make sense to me. Could someone explain it more thoroughly please?

Also, how do I run the sample code in my excel VBA??

I don't mean to be a pest, I just would like to understand more about VBA and it's relation to Excel.

Thanks
E

[lucas]

Hi E,
Do you know where the visual basic editor is in excel?

[Bob Phillips]

See http://www.mvps.org/dmcritchie/excel/getstarted.htm

[dickep]

xld, thanks for the link. It is very informative but I am still struggling with the combobox entity.

So, with the following code - that does NOT work - what can I do to make it work?

[vba]'This is to stop screen flashing while creating form
Application.VBE.MainWindow.Visible = False
Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)
'Create the User Form
With TempForm
.Properties("Caption") = "My User Form"
.Properties("Width") = 450
.Properties("Height") = 300
End With
For X = 0 To 9
Set NewComboBox = TempForm.designer.Controls.Add ("Forms.combobox.1")
With NewComboBox
For Row = 4 To 10 'Each cell in the range
.AddItem Sheets("Sheet1").Cells(Row, 34)

Next Row
End With
Next
'Show the form
VBA.UserForms.Add(TempForm.Name).Show[/vba]

P.S. this was taken from another site.

Lucas, yes I know where the VBE is and have been doing some VBA things.

Thanks again.
E

[Norie]

Why are you trying to create a userform and combobox on the 'fly'?

[lucas]

E, You should read through this thread and download the files and see if you can get started from there...
http://www.vbaexpress.com/forum/showthread.php?t=15915

I'm with Norie on the code you have posted...I would abandon it and start fresh.

[dickep]

Thanks all.

I looked over the link from lucas and it is pretty straight forward.

Norie, the reason I am trying to do a userform and combobox on the fly is (1) it is an excercise I thought would be a great learning experience, and (2) I have a project that I did using option buttons and would like to use comboboxes for size of userform (the last one almost went to two forms and I really would like to avoid that if possible). Also, the project may change (data wise that is) and I would like to not have to recode/update the userform and/or code.

Thanks again
E

[dickep]

One more thing.

I still have a lot of learning concerning VBA and especially listbox and combobox usage.

If there is a good tutorial site that can give not only basic stuff but also the "nuts and bolts" and advanced stuff, it would greatly be appreciated.

I always enjoy learning and really start pushing when I can't figure something out.

Again, thanks
E