Resize ListBox Columns

Hi Everyone:
Just wondering if there's an easy way in Access 2000 to resize listbox columns if necessary to show all information - like you can in a table by clicking, or by the columns autoformatting. Thanks for your help.

Post your answer or comment

comments powered by Disqus
There is a nice sample database on here to resize the listbox columns on the fly, is there a way to add a label or button above to change the sort order (Done no problem) but have the buttons resize with the columns??


	Me.CmdName.Width = Me.[lstTest].Column(0).Width


Me.CmdName.Width = Me.lstTest.Column(0).Width

Where CmdName is the button above and lstTest is the listbox



i want to allign a listbox column data, associated with a table as right, center & left, is it possible? if possible please help me

thanks in advance

Hi All,

This is the function i'm using for summing a listbox column:

Function TonSum() As Variant
Dim I As Integer, J As Integer, ctl As Control
Set ctl = Me.lstDrivera2
J = ctl.ListCount - 1

TonSum = 0
For I = 1 To J
TonSum = TonSum + ctl.Column(8, I)
Next I

End Function

The problem i have is if one the records in the listbox doesn't have a value (is null) then Access gives me a 'Type Mismatch' error. Can anyone suggest how to treat the null value so it excludes it from the sum?

I have Access 2003 on Win XP.

I am using a ListBox control on a form. I can create multiple columns but cannot resize the columns. My data source is a value list, populated via VB code. Is there any way I can let my users resize columns? Data may be too large to fit with the preprogrammed column widths.



I use this class: to autosize the columns of my listbox. I use this listbox to populate data from my table using a textbox and a SQL statement.

The problem is this code gives a error sometimes. The strange thing is I can't reproduce it. (the error is something like object not defined or block with...unknown) The code is updated every time something in the textbox changes (so very often).

I want to add some error handling to the code, so I tried (in the autosize sub) :

on error goto ErrorNr:



The problem is when I execute the code every time this sub is called a empty message box pops up? Is there a error in this code or am I doing something wrong?

So my questions are:

Is there anyone who is familiar with this code?

How can I built some good error handling into the code.

Thanks in advance.


I have a query that I want to read a column of data in a listbox. The bound column is already set for something else so I need to select the column to read. I have


but I get the error Underfined Function in expression.
Is it not possible to do this?


I have a problem. I have a listbox that is defined as rowsource for "cash receipt tickets" Each ticket can have multiple "lineitems' two tables..

Crlineitems & Crticket

I have a function after updating a single ticket which calculates the sum of column 5 in the listbox. Were talking maybe 10 line items tops.. I keep getting an OVERFLOW error. I checked and there are no null values in the table. Here is the code. Any Idea?

Public Function UpdateCashReceiptTicket()

Dim listrs As Integer
Dim listsum As Integer
listrs = Form_CRTICKET.CrListLineItems.ListCount
''x = 1
listsum = listsum + (Form_CRTICKET.CrListLineItems.Column(2, x))
x = x + 1
Loop Until x = listrs

Form_CRTICKET.TicketCount = Form_CRTICKET.CrListLineItems.ListCount
Form_CRTICKET.TotalDeposit = listsum

End Function

I have a listbox filled form a query. It has 4 columns. I want the value from the last column and use it to open a report. I can open a report ok, the issue is that
Me.lstThings.Column(3) is always null. Read a stack of posts out here and appear to be doing it right. What am I missing?

This is pretty self explanatory. I'm trying to set a textbox equal to my selected row of my 2-column listbox. Column1 is a city Column2 is a state.

So if the listbox has a row like:
San Diego | CA

What I have now: Me.textField.Value = Me.listBox.Column(1) & ", " & Me.listBox.Column(2) Shows: "CA, " in the textbox

If i change this to:Me.textField.Value = Me.listBox.Column(2) & ", " & Me.listBox.Column(1) I get closer with: ", CA"

#1 - How do I get my city listed
#2 - Why would column2 go before column1

Thank you in advance,

is there anyway to have more than one column appear on a listbox or would this be more suited for another control? (through vba ofcourse)

Thanks in advance

I have the following SQL code:

	SELECT tblCustomers.customerID AS ID
FROM tblCustomers
WHERE (((tblCustomers.customerID)=1));

The select query is the row source of a listbox. How do I get the ID alias to appear in the column head? The query is going to be created using Querydef so it's not fixed otherwise I could have made the Caption property equal to ID.

I have several forms that use both combo and listboxes. Does anyone know of a method to dynamically resize the height of listboxes? On my forms, I have placed several group (or frame) items, and it would be great to have the list boxes within a respective group (or frame) dynamically expand their height for ease of readability by my users.

Any help or advice is truly appreciated.

This one is an easy one but a stumper as I cannot find the answer in either the help files, the Access Cookbook, or this forums' past posts so....

What is the property used to keep the last column in a list box from being really, really long for no reason?

I have set the last columns width to 1 in but it is huge and as a result makes the horizontal scroll function way, way longer than it needs to be. I have also tried to set the listbox width and this has not solved the problem either.

Anyone know the way to get around this?

Thanks in advance,

Hey, I'm getting hung up on this and it's getting a little bit frustrating...

I have a form with a list box (lstTookCourse) that is populated by a query. (the populating query works fine)

The listbox has 3 columns, a personnelID (number field and an auto-number PK for the personnelID table), a last name, and a first name.

I want to be able to run an UPDATE or an INSERT statement on a different table, but this depends upon whether a record already exists (then do update) or if I need a new record (then do INSERT)

Anyway, the table in question has two fields, which act as a joint primary key.

One of the PKs I can get from a combo box somewhere else on the form.

The problem is getting the value of the other PK, which is the first column in the list box.

To loop through the selected items in the listbox I use the code:

Dim item As Variant

For Each item In lstTookCourse.ItemsSelected

Next item

I can reference the value I need with:
lstTookCourse.Column(0, item)

The problem is, I don't know how to reference this number in the WHERE clause of a query that I call within the loop.

So, my question is: how do I reference the number in the WHERE clause of a query that I call from within the for loop?

I tried [Forms]![Form Name]![lstTookCourse].[column(0, item)]

But obviously, that doesn't work.

I also tried the list boxes .value method, but had no luck with that either.

Any suggestiong?

Sorry if I went rambling on a bit, but I wanted to give as complete a picture as possible.

Thank you in advance.

I have a form with multiple tabs in the detail and a listbox it the header. Each tab has a subform on it. I am trying to limit a record set in all subforms based on a column in the list box. The idea is that I am setting and changing the context of all of the tab subforms based on the current row in the listbox.
It works fine for a straight table based subform, but not for a INNER JOIN based subform. I tried using the lisbox column “[Forms].[my_form].[my_listbox].[Column(0)]” but it just pops up a dialog box asking me to enter the data, I tried setting the value in a function and calling the function from the criteria, but it does not work. I get “Undefined function my_function in expression”. Is there a way to accomplish what I set out to do?

Hi there

I have a listbox and all is well - the only problem is that the column heads are a little 'ugly' - my column names are usually something like 'catagoryName', 'productType' and 'price' instead of looking pretty, like 'Catagory Name' and 'Product Type' and 'Price' respectively.

Is there any way to edit/format the title/column heads without having to change all the tables and queries etc, so they look a littler prettier?

Many thanks



i have a listbox..

i want to change the order of the records whenever my user clicks on one of the fields (columns)


my records are selected thru a query that puts them in order of date created (descending)

one of the fields that displays in the listbox is jobdate.

if my user clicks any record in the job date column, can the listbox be re-queried?

My form has a multiselect listbox with 5 columns
The form also has an unbound textbox

I need place all of the selected rows (column 1 thru 5) into the test box with a VbCrLf after each row

I can use a jumping off point


In a table I have a numerical field called [rowcount] with

Field Size = Long Integer
Format = Standard
Decimal Places = 0

I use a query based on the table as the record source of a listbox to select some of the fields.

When I open the query, the [rowcount] column appears as it is set in the table e.g.


However in the listbox it appears as


How do I resolve this?

Is there any way to make the column lengths in an access 2007 listbox the same as in access 2003?


Hi all,

Im not sure what I want to do here is possible but hopefully I am wrong.
Im basing a listbox row source on a cross tab query. The column names of the cross tab query change depending on criteria selected in a form.
The data is all number and in the cross tab query its set to 0 decimal places.
The rowsource is showing the decimal places and I want to stop that using ‘#,###’.
In the rowsource Im using “qry_CrossTab.*” to include all my fields.
How can I format this to what I want? Format([qry_CrossTab].[*],”#,###”) doesn’t seem to work.


I am trying to filter a form based on a listbox selection made by the user:
PHP Code:
Me.Filter = "[FiscalYear] = " & Me.listbox1.Column(0) & " And [Num1] = " & Me.listbox1.Column(1) & " and [Num2]= " & me.listbox.column(2) 

This works for me ONLY if I have one column in the listbox by which I wish to filter. If I try to filter by more than one field as above, it fails. My listbox has the columns FiscalYear, Num1, Num2. When I specify a filter criteria for my detail section using all the three fields I wish to filter, here is what happens:

-If I select a record from the listbox with a fiscalyear that is different from the fiscal year of the current record, then it jumps to the FIRST record with the fiscal year specified by my listbox selection, which is NOT necessarily the record with the right Num1 and Num2- it just jumps to the first record with the relevant fiscal year.
-If I select a record from the listbox with a fiscalyear that is the same as the fiscal year of the current record that I am on, then it doesn't filter at all- just stays as it is.

Can anyone spot why my piece of code is filtering only on the first part of the criteria and ignoring the other two? This code is sitting in the on-click event of the filter button in the header of the form.

Thank you very much.

What I have: I have a main form with 3 subforms viewed as datasheets.

I have read the post about hiding columns by using me..columnhidden = True and that kind of works. Once the user goes to resize the columns they can get the hidden columns back. This is a problem if I'm trying to hide a priority column only admins should see.

Is there a way to specify the width of a column and restrict the user from resizing it? That way the user can not resize and get hidden columns back.

If there is a better way if doing this please let me know. Ultimately, I just don’t want the user resizing or unhiding columns I have sized and hidden.


i have a url file location to a pdf file on the 3rd column of a listbox.

is there anyway of opening the file from a button, refering to the the highlighted record row 3rd column pdf file location

i know that me.lstBox.column(2) gets me to the column but not the code to open the pdf file for the highlighted record

any help would be appreciated

Not finding an answer? Try a Google search.