PDA

View Full Version : multicolumn combobox range



dickep
11-02-2007, 09:06 PM
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.

:banghead: 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:dunno . Then I need to know if I can relate the displayed/selected item in the combobox to the "tag" for processing later.
: pray2:
Thanks
E

Bob Phillips
11-03-2007, 01:09 AM
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



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

dickep
11-03-2007, 09:43 AM
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, :bug: 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:yes
E

Norie
11-03-2007, 09:53 AM
Can't you just set the RowSource to AJ9:AJ18?

Or is there more to it?

dickep
11-03-2007, 12:59 PM
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.:dunno

Thanks again
E

Bob Phillips
11-03-2007, 07:02 PM
Didn't Nories example use the last column?

dickep
11-03-2007, 07:58 PM
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
11-03-2007, 08:30 PM
Hi E,
Do you know where the visual basic editor is in excel?

Bob Phillips
11-04-2007, 04:53 AM
See http://www.mvps.org/dmcritchie/excel/getstarted.htm

dickep
11-04-2007, 01:54 PM
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?

'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

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
11-04-2007, 01:59 PM
Why are you trying to create a userform and combobox on the 'fly'?

lucas
11-04-2007, 03:10 PM
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
11-04-2007, 05:14 PM
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
11-04-2007, 06:09 PM
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.:beerchug:

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

Again, thanks
E