Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel VBA Multi Column ComboBox
I have a combo box on a user form and have the box filling in with
concatenated data and woudl liek to make it into columns instead. Here is the code that I have. The loop goes through and takes out all of the "Item Numbers" which are in every 4th row. Then I will also need the Quantity which is in i.offset(1,0) and the Date in i.offset (2,0) and finally if the interior color is 4 I want the word "Promise". I hope you can help. For Each i In Rng1 'fills in items from run Combo at bottom from cut area 'Check if it is a promise If i < "" And i.Row Mod 4 = 0 Then If i.Interior.ColorIndex = 4 Then Prom = "Promise" Else Prom = "" End If AvailableItems.AddItem (i & " " & i.Offset(1, 0) & " Cut " & _ i.Offset(2, 0) & " " & Prom) End If Next i Thanks, Jay |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel VBA Multi Column ComboBox
HI
You need to do most of the work in VB in the Properties window for the combo box. First, set ColumnCount property to the number of columns, in your case 4. now set the ColumnWidths to best fit your data in points e.g. 100,40,55,120. Now to populate the combobox create an array with the same number of columns i.e. and then fill the "row" entries where they apply. For example if you have 12 rows you would define the array as: Dim MyArray (1 to 12, 1 to 4) Once the array is filled with the info you need simply use the following: ComboBox1.List = MyArray If this helps please click "Yes" <<<<<<<<<< "jlclyde" wrote: I have a combo box on a user form and have the box filling in with concatenated data and woudl liek to make it into columns instead. Here is the code that I have. The loop goes through and takes out all of the "Item Numbers" which are in every 4th row. Then I will also need the Quantity which is in i.offset(1,0) and the Date in i.offset (2,0) and finally if the interior color is 4 I want the word "Promise". I hope you can help. For Each i In Rng1 'fills in items from run Combo at bottom from cut area 'Check if it is a promise If i < "" And i.Row Mod 4 = 0 Then If i.Interior.ColorIndex = 4 Then Prom = "Promise" Else Prom = "" End If AvailableItems.AddItem (i & " " & i.Offset(1, 0) & " Cut " & _ i.Offset(2, 0) & " " & Prom) End If Next i Thanks, Jay |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel VBA Multi Column ComboBox
How do you do this if it is not a fixed number of rows? I never know
how many items are going to be in a column until the macro starts countign them. For instance I have a range of items down A starting from 52 and going to the end of the sheet. I want to list all item Numbers which will be every 4th row and the rest of the columns are offset from this number. Hwo woudl I set the Array if I do nto knwo how far down it goes? Thanks, Jay |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel VBA Multi Column ComboBox
What you do is initialise the array as a dynamic array with the Dim statement
as below. Then run your procedure to count the number of rows. Now use the "ReDim" statement to set the array size and then fill the array as required. Sub MySub() Dim MyArray() <Your function to count the rows ReDim MyArray (1 To NumberofRows, 1 To 4) <Your function to fill array ComboBox1.List = MyArray End Sub If this helps please click "Yes" <<<<<<<<<< "jlclyde" wrote: How do you do this if it is not a fixed number of rows? I never know how many items are going to be in a column until the macro starts countign them. For instance I have a range of items down A starting from 52 and going to the end of the sheet. I want to list all item Numbers which will be every 4th row and the rest of the columns are offset from this number. Hwo woudl I set the Array if I do nto knwo how far down it goes? Thanks, Jay |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting a Word doc to a multi-column Excel spreadsheet | Excel Worksheet Functions | |||
fill combobox depending on selection from another combobox | Excel Discussion (Misc queries) | |||
How do I make multi column stacked graphs in Excel | Charts and Charting in Excel | |||
match in multi-column and multi-row array | Excel Discussion (Misc queries) | |||
Selecting any cell in a column I want my ComboBox to show. How? | Excel Discussion (Misc queries) |