The Linux Page

Automatic Datasheet Columns Width with MS-Access VBA

You may have noticed that whenever you create a Datasheet you can double click on the right edge to resize the column to the widest text currently defined in that column.

This is neat but if I create a table with 10 columns, that's work. Especially if I need to reload data in that table each time I use it, 10 times an hour. (i.e. 100 x double click would definitively kill me!)

Of course, you may not need to resize all 10 columns each time, but wouldn't it be neat to have the columns width auto-adjust with the data you put in that Datasheet table?!

I think it would be!

The solution is actually very simple. Assuming your Datasheet is defined in a Subform named DS that has 10 columns named C1 to C10 you can write:

    DS.Form.C1.ColumnWidth = -2
    DS.Form.C2.ColumnWidth = -2
    DS.Form.C10.ColumnWidth = -2

This is actually defined in the documentation, but I just couldn't find it. There is nothing in the MS-Access documentation that says automatically resize columns or auto-resize or auto-size column, auto-width, column width, etc.

The fact is they do not even explain the -2 value, but they show an example with it! They only specify -1 as the default width.

There are cases when you will eventually need to resize the DAO column too. In that case you need to set the property named "ColumnWidth" on the DAO field. But frankly, that's another story altogether! You can see a sample code at the end of this post.

Re: Automatic Datasheet Columns Width with MS-Access VBA

Or select the entire worksheet by clicking in the top left selection square (left of 'A' and above '1') and then double-click on the right edge of any of the columns!

Re: Automatic Datasheet Columns Width with MS-Access VBA

It has been a while since I worked with MS-Access...

From what I understand in your question, I would probably look into writing a function that loops over the columns and set their width to -2. I would attach that function to the Load event (the event that tells you the data is now loaded.)

The width has to be setup after the data is loaded because otherwise it won't know what the best fit is. If you are writing code to load the data, then you can also do the column sizing right after the function(s) used to load the data.

Re: Automatic Datasheet Columns Width with MS-Access VBA

Hi and thanks for your advise,

I have a question please,,
What is the solution (with VBA Code Access 2010)
for set "ALL columns width"
in "Best Fit"
due to load a form
(especially in datasheet view)?

Many Thank you,,