How populate multi-column Listbox control?

Ok, I'm back with more questions about the Listbox control.  The
help documention is not very helpful, so I gotta keep asking here,
haha.

Anyways, my listbox control is set to have 3 columns, but I just can't
figure out how to put data into any column except column 1.  How the
heck can I put data in columns 2 or 3???

BTW, I tried the following code which I found through Google, but it
doesn't work:

   ListBox1.AddItem  "here is some text"
   ListBox1.List(1, 1) = "more data" 
   ListBox1.List(1, 2) = "even more data" 

Please help.  thank u


0
Robert
3/6/2010 8:05:36 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
2717 Views

Similar Articles

[PageSpeed] 57

When I do this kind of thing, I'm usually looping through something.

So...

Option Explicit
Private Sub UserForm_Initialize()
    Dim iCtr As Long
    
    With Me.ListBox1
        .Clear
        .RowSource = ""
        .ColumnCount = 3
        
        For iCtr = 1 To 5
            .AddItem "A" & iCtr
            .List(.ListCount - 1, 1) = "B" & iCtr
            .List(.ListCount - 1, 2) = "C" & iCtr
        Next iCtr
    End With
End Sub

Listcount is how many items there are (1 then 2, then 3, ..., then 5 in this
case), but the first item in the list is item 0, so we subtract 1 from the
current listcount.

Robert Crandal wrote:
> 
> Ok, I'm back with more questions about the Listbox control.  The
> help documention is not very helpful, so I gotta keep asking here,
> haha.
> 
> Anyways, my listbox control is set to have 3 columns, but I just can't
> figure out how to put data into any column except column 1.  How the
> heck can I put data in columns 2 or 3???
> 
> BTW, I tried the following code which I found through Google, but it
> doesn't work:
> 
>    ListBox1.AddItem  "here is some text"
>    ListBox1.List(1, 1) = "more data"
>    ListBox1.List(1, 2) = "even more data"
> 
> Please help.  thank u

-- 

Dave Peterson
0
Dave
3/6/2010 8:30:22 PM
Dave,

Thanks, that worked great!

BTW, do you know if I can add gridlines or something to the listbox
so I can see the dimensions of each cell in the listbox??  I almost might
want column 3 to be longer than the others.

Thanks again!

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:4B92BB5E.CB0C83F@verizonXSPAM.net...
> When I do this kind of thing, I'm usually looping through something.
>
> So...
>
> Option Explicit
> Private Sub UserForm_Initialize()
>    Dim iCtr As Long
>
>    With Me.ListBox1
>        .Clear
>        .RowSource = ""
>        .ColumnCount = 3
>
>        For iCtr = 1 To 5
>            .AddItem "A" & iCtr
>            .List(.ListCount - 1, 1) = "B" & iCtr
>            .List(.ListCount - 1, 2) = "C" & iCtr
>        Next iCtr
>    End With
> End Sub
>
> Listcount is how many items there are (1 then 2, then 3, ..., then 5 in 
> this
> case), but the first item in the list is item 0, so we subtract 1 from the
> current listcount.
>

0
Robert
3/6/2010 9:23:54 PM
You can use ColumnCount to set the number of columns, ColumnWidths to
set the columns widths and pass an array to List for a list box. E.g.,


Private Sub UserForm_Initialize()
    Dim NumRows As Long
    Dim NumCols As Long
    Dim Arr() As String
    NumRows = 2
    NumCols = 3
    ReDim Arr(1 To NumRows, 1 To NumCols)
    Arr(1, 1) = "r1 c1"
    Arr(1, 2) = "r1 c2"
    Arr(1, 3) = "r1 c3"
    Arr(2, 1) = "r2 c1"
    Arr(2, 2) = "r2 c2"
    Arr(2, 3) = "r2,c3"
    With Me.ListBox1
        .ColumnCount = 3
        .ColumnWidths = "100;30;30"
        .List = Arr
    End With
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
	Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com



On Sat, 6 Mar 2010 13:05:36 -0700, "Robert Crandal" <nobody@gmail.com>
wrote:

>Ok, I'm back with more questions about the Listbox control.  The
>help documention is not very helpful, so I gotta keep asking here,
>haha.
>
>Anyways, my listbox control is set to have 3 columns, but I just can't
>figure out how to put data into any column except column 1.  How the
>heck can I put data in columns 2 or 3???
>
>BTW, I tried the following code which I found through Google, but it
>doesn't work:
>
>   ListBox1.AddItem  "here is some text"
>   ListBox1.List(1, 1) = "more data" 
>   ListBox1.List(1, 2) = "even more data" 
>
>Please help.  thank u
>
0
Chip
3/6/2010 9:43:21 PM
I don't think Excel's listbox allows that kind of formatting.

And take a look at Chip's suggestion.  He builds the array before and just loads
it in one fell swoop.

(And take note of his .columnwidths line, too.)

Robert Crandal wrote:
> 
> Dave,
> 
> Thanks, that worked great!
> 
> BTW, do you know if I can add gridlines or something to the listbox
> so I can see the dimensions of each cell in the listbox??  I almost might
> want column 3 to be longer than the others.
> 
> Thanks again!
> 
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:4B92BB5E.CB0C83F@verizonXSPAM.net...
> > When I do this kind of thing, I'm usually looping through something.
> >
> > So...
> >
> > Option Explicit
> > Private Sub UserForm_Initialize()
> >    Dim iCtr As Long
> >
> >    With Me.ListBox1
> >        .Clear
> >        .RowSource = ""
> >        .ColumnCount = 3
> >
> >        For iCtr = 1 To 5
> >            .AddItem "A" & iCtr
> >            .List(.ListCount - 1, 1) = "B" & iCtr
> >            .List(.ListCount - 1, 2) = "C" & iCtr
> >        Next iCtr
> >    End With
> > End Sub
> >
> > Listcount is how many items there are (1 then 2, then 3, ..., then 5 in
> > this
> > case), but the first item in the list is item 0, so we subtract 1 from the
> > current listcount.
> >

-- 

Dave Peterson
0
Dave
3/6/2010 11:04:52 PM
Reply:

Similar Artilces:

Sum of every nth cell in a column
I am trying to figure out the formula for adding the nth cell in a given column. I am using the formula below to add every 4th cell but it is just one that I found on line. Can someone explain to me the components of this formula so I can adapt it? =SUM((C2:C213) * (MOD( ROW( C2:C213)+1, 4)=0)) That is an array formula so it must be committed with Shift + Ctrl + <Enter>. here is how it works... Moving down through cells C2:C213 it looks at each 2 things. The value of the cell and the row that cell is on. If the row that cell is on is +1 is evenly divisible by 1 then ...

match first and last name and insert column into existing sheet
I have two sheets, Sheet1 and Sheet 2 In Sheet1 and Sheet2 Last name and first name are in column A and B (respectivly) However, Sheet2 is a pivot table. I need to insert column I and J of Sheet2 into Sheet1 if the first and last name matches exactly. I am having issues with the funtion, and do not know if I need to do something differently because it is a pivot table Use the GetPivotData function. See here: http://www.contextures.com/xlPivot06.html HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''....

3-D Column Chart
I have created a 3-D Column Chart in an Excel file and I'm wondering if it is possible to make the bars stacked. To be more specific, I have a 3-D column graph with 16 columns (4 columns X 4 columns). Is it possible to now divide each column into 4 parts? Even if you could make a 3D stacked column chart, it would be nearly impossible to interpret. Actually you can fake it, but in the interests of good data presentation practice I no longer share the technique. Just because you can do something does not mean you should. An alternative for a 3D unstacked column chart is a simple 2D...

correct total column in flitered list
This is a multi-part message in MIME format. ------=_NextPart_000_000D_01C75DB4.A3C658D0 Content-Type: text/plain; charset="windows-1256" Content-Transfer-Encoding: quoted-printable correct total column in filtered list Dear friends, I have a list(table in 2007) as bellow Serial Item Code Import Export Remain =20 1 10 20 =20 20 =20 2 20 =20 10 10 =20 3 10 30 =20 40 =20 4 30 10 =20 50 =20 5 20 =20 20 30 ...

How are dialog controls created ?
In my Dialog I have a control subclassed from CEdit but neither Create() nor PrecrateWindow() gets ever called. Why is this? The only way to modify properties I found is to override PreSubclassWindow(). WindowProc() fails too. TIA, Hans LRESULT CPwdEdit::WindowProc(UINT message, WPARAM wp, LPARAM lp) { switch (message) { case WM_CREATE: TRACE0("WM_CREATE\n"); // never get here break; . . } That's right. Neither can be called, because the create happens long before you subclass, and consequently there can be no PreCreate call. PreSubclassWindow is the correct place to...

Getting Active Control During Current Event
I have some code in my form's On Current event which changes focus to a particular control. I want the control to remain where it was when the user moved to the new record. But using Screen.ActiveControl or Me.ActiveControl in the OnCurrent event results in the error "the expression you entered requires the control to be in the active window" (2474). How can I determine the control that had the focus before moving to the new record, so that I can change it back there afterwards? Thanks! I think what you actually want is Screen.PreviousControl -- Doug Steele, Microsoft...

listbox data from a closed file
i have a listbox in an excel file on my desktop and want to open a excel file C:\A1.xls having found the info on this site i still find difficulties in openin said file can any one point me in the correct direction to go forward ********************************************************** From a closed workbook that is opened and closed again without the use noticing it With the macro below you can fill a ListBox control with input fro another closed workbook. The macro opens the closed workbook without the user noticing it, get the values for the ListBox and closes the source workbook without...

In a spreasheet, how do you move a column around?
Gurus, In a spreadsheet, how do you move a column around? I have multiple columns. A "Cut and paste" won't do it. -- Spin Select the column, Ctrl-C it, select the column to the right of where you want to move it, then select Insert>Cut Cells -- Spreadsheet ------------------------------------------------------------------------ Spreadsheet's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34730 View this thread: http://www.excelforum.com/showthread.php?threadid=556674 how about cut and INSERT cut cells -- Don Guillett SalesAid Softwa...

Keypress Event on Control
After setting the keypress event on a textbox control (my "special" control), and adding code to react to pressing the Enter key or the "+" key, I see that the reaction is happening before the control even gets the focus, or so it appears. I'm trying to set up the control to act as a close button if it has the focus and the Enter key is pressed, or to simply go to the next control if it has the focus and the "+" key is pressed. But when the focus is on the control ahead of this one, and the Enter key is pressed to move to my "special" control, the ...

Move Outlook 2003 Flag Status Column
Is there any way to move the Outlook 2003 Flag status column from the right side of the columns list to the left side. It appears that column is the only one that is static. From the look of things the flag column will always show in the right margin no matter where it shows in the field list "Jim Smith" <jim.smith@lcu.edu> wrote in message news:Ox7XCuQbFHA.1660@tk2msftngp13.phx.gbl... > Is there any way to move the Outlook 2003 Flag status column from the > right side of the columns list to the left side. It appears that column is > the only one that is sta...

Add inner control event.
I have a control. On that control I have a button. That is a control (I did it by : add new class, and inherits DataGridView). On the new dataGridView there is a new property : Button b. I want to catch events of that button (from the newDataGridView). What should I do on the eventHandler ? : AddHandler FNewButton.Click, btnNew_Click .... Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Me.Click (Handles what? Me.Click is not right, and b.click cannot be compiled). Thanks :) Am 24.04.2010 17:58, schrieb Mr. X.: &...

Column charts
Is there a way of formatting a column by the value - eg green for >= 98%, red for <98%? Jon Peltier tell all at http://peltiertech.com/Excel/Charts/ConditionalChart1.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Bernster" <Bernster@discussions.microsoft.com> wrote in message news:2D972C0D-4B24-4F18-B6D1-251D4C357D8D@microsoft.com... > Is there a way of formatting a column by the value - eg green for >= 98%, > red > for <98%? Hi, There are two possible questions here - 1. Can you f...

GridView not populating, but not all the time, clearing cache fixe
I have essentially a working website. It has an Administration page, a CreateUser page, a Login page, a HospitalAccess page (for searching for a list of reports and displaying them in a GridView) and a FinalChart page which displays the report the user clicks on in the HyperlinkBoundField in the GridView from the HospitalAccess page. So, essentially, we have a site with 2 user pages, Hospital Access handles searching and displaying of results in a Sortable GridView with multiple pages. The left-most column in the gridview is a hyperlink bound field pointing to the finalcha...

Match Columns
Hi, I have two columns with numbers. I need to know what numbers in column A is the same in column B. Could this be done by highlighting? or some other way? Thanks for your help! Look here: http://www.cpearson.com/excel/Duplicates.aspx -- Kind regards, Niek Otten Microsoft MVP - Excel "Lindsey" <Lindsey@discussions.microsoft.com> wrote in message news:543756AA-9E6A-4CB3-B0B8-2B8DD0C838DB@microsoft.com... > Hi, I have two columns with numbers. I need to know what numbers in column > A > is the same in column B. Could this be done by highlighting? or some other...

Provide a feature to increase the number of columns in a workshee.
Provide a feature to increase the number of columns in an Excel worksheet beyond 256 to either a higher maximum like 512 or 1024 or unlimited. Not possible, 256 is the max. If you want more, you need a re-design or another product. -- HTH RP (remove nothere from the email address if mailing direct) "Exceller" <Exceller@discussions.microsoft.com> wrote in message news:B393C8E0-8618-4D9C-906B-2F9163384B35@microsoft.com... > Provide a feature to increase the number of columns in an Excel worksheet > beyond 256 to either a higher maximum like 512 or 1024 or unlimited....

Create another sheet with info from a "Job #" column
Good aftermorning to you all. It seems the more I get done the more that is added to my list of things to do (don't ya hate it when that happens?) Here's what's on today's TODO list: I am working on a TruckLog, had everything running perfectly then was told that I need to add a column to let us know which Job the trucks are runnin' to. Fine, that's easy. But the problem is that they want it set up so that when a Job# is entered in the column, it creates a new sheet (the sheet has to come from an outside source: i.e. another Excel worksheet), then the sheet has to be...

Using MFC ActiveX control in ASP
I'm using VS.Net 2003 to create a very simple ActiveX control in MFC. The control has no ui and just a few properties and methods. What do I need to do to make it work in a classic ASP page? Currently any time I try to set a property on my control {after calling 'Set obj = Server.CreateObject(...)'} I get this error: Error Type: (0x8000FFFF) Catastrophic failure I can successfully use my control from a C# Winforms app. What am I missing? -Brett- > What do I need to do to make it work in a classic ASP page? It needs a standard COM and/or an IDispatch interface. Does i...

column of text cells
I have a column of country names entered as text, and I need to graph the number of occurances of each country. Help please! Got it. Thanks anyway! ...

Adding a control variable
Hello all, Under VC++ 7.1, I've added a checkbox to a dialog. Using the dialog editor, I am not trying to add a control variable for this check box. However, when I right-click on the check box and select "Add Variable", the resulting wizard dialog does not allow me to select "Control Variable" (it is grayed out). What do I need to do to get a control variable for this check box added to my class? Thanks, Dave I've never seen that before - are you able to duplicate this problem with other projects too? -- Regards, Nish [VC++ MVP] "Dave" <bett...

New column in query
I have the following columns in my query empid, courseA, datecompA, courseB, datecompB, courseC, datecompC. What I need is an extra column that will have an indicator Yes if any of the 2 dates(i.e., datecompA, datecompB, datecompC) have dates, blank if all 3 dates have dates and No if only of the dates has a date. Based on your description of the fields you have in your query, it sounds like you are working from ... a spreadsheet! Is this a query based on a "linked table" (linked to an Excel spreadsheet)? If not, and if this is an Access table, your database would benefit ...

Calendar Control 11.0 #2
Hi, I was wondering if anybody would direct me to some information on the Calendar Control object in Excel. I have inserted the calendar object and what I would like to do is be able to click on a date and have the object move me to another sheet in the workbook that would reflect information for that day. Is this possible? Thanks, MCorn Hi MCorn Look here http://www.rondebruin.nl/calendar.htm -- Regards Ron de Bruin http://www.rondebruin.nl "MCorn" <MCorn@discussions.microsoft.com> wrote in message news:D0C6F80A-F68D-4650-B7E5-1FA9789E4E10@microsoft.com... > Hi, &...

Printing a Column Chart
Using Word 2007. Print a bar chart as in DOCX format - the background color in one of the columns of a chart is missing. Same document saved as a DOC file, it prints fine. Any assistance would be appreciated. ...

Filter for only one column
Hi I'm a normal excel user, and i would like to know if there's an eas way to apply a filter to ONLY ONE column. Autofilter just seems t apply it to all the cells in a row. If it implies adding some code, don't care, but please try to keep it as simple as possible. Thx for the help -- Magite ----------------------------------------------------------------------- MagiteK's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3028 View this thread: http://www.excelforum.com/showthread.php?threadid=49950 All you need to do is highlight a single column (cli...

multi sheet graphing
Does anyone know if you can graph acrosss worksheets i.e. if you have 10 sheets with data in box c10 can you graph all c10 boxes vrs the tab on the worksheet or other specified variable. Hi John, I tried putting Sheet1:Sheet3!A1 as the source for a simple coloumn chart. No go - the references is not valid. Bernard "john cowpar" <john.cowpar@gesm.ge.com> wrote in message news:04e001c38de7$dac39b40$a401280a@phx.gbl... > Does anyone know if you can graph acrosss worksheets > i.e. if you have 10 sheets with data in box c10 can you > graph all c10 boxes vrs the tab o...

Pivot Table Not Populating Correctly
I run a pivot table with data downloaded from the web but today for some reason the pivot table will not populate correctly. After I create the table, I try to drag over a variable "cost" and it populates every cell as '1' and rather than labeling the column "cost", it labels it "count of cost". I can see from the worksheet that it is taking the data from that the cost is correct but for some reason the table will not populate with it. Why is this happening and what can I do to fix it and prevent it from happening again? Thanks, Chris Chris, ...