listbox - code to adjust column widths

  • Thread starter Thread starter Roger on Excel
  • Start date Start date
R

Roger on Excel

[Excel 2003]
I use listboxes to display tables of data.

Is there a way to adjust individual column widths such that one can make the
columns fit - some of my columns need to be narrow and some wide.

Thus far I have only been able to make the first column width adjusted
(ColumnWidth Property) to what i want, but the others seem to be set to a
predefined default width.

Surely one can use code to adjust all the widths??

Roger
 
To manually adjust the columns of a listbox in the properties window, if you
have three columns:

ColumnWidth | 20, 40, 25

Would set column 1 to 20, col 2 to 40 and col 3 to 25
 
That should be delimited by semicolons, not commas.

20; 40; 25

In Code:

UserForm1.Listbox1.ColumnWidths = 20; 40; 25

The default measurement is in points. You have to specify if you want to
use inches. See VBA help for details.


JLGWhiz said:
To manually adjust the columns of a listbox in the properties window, if
you have three columns:

ColumnWidth | 20, 40, 25

Would set column 1 to 20, col 2 to 40 and col 3 to 25



Roger on Excel said:
[Excel 2003]
I use listboxes to display tables of data.

Is there a way to adjust individual column widths such that one can make
the
columns fit - some of my columns need to be narrow and some wide.

Thus far I have only been able to make the first column width adjusted
(ColumnWidth Property) to what i want, but the others seem to be set to a
predefined default width.

Surely one can use code to adjust all the widths??

Roger
Click to expand...
Click to expand...
 
[Excel 2003]
I use listboxes to display tables of data.

Is there a way to adjust individual column widths such that one can make the
columns fit - some of my columns need to be narrow and some wide.

Thus far I have only been able to make the first column width adjusted
(ColumnWidth Property) to what i want, but the others seem to be set to a
predefined default width.

Surely one can use code to adjust all the widths??

Roger
Click to expand...

Set up the sheet you wish to have column widths copied from, and select
the top row, or only those cells you wish to copy the column widths of.
Copy then paste special in the sheet you want with "column widths" as
what you are copying. That is the hand mode. The recorded code I got was:

Sub ColumnAtribCopy()
'
' Meant to copy column widths from current
' sheet to specified sheet
' (currently sheet2)

Rows("1:1").Select
Selection.Copy
' target sheet name can be specified below
Sheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone,
_
SkipBlanks:=False, Transpose:=False
Range("A1").Select
End Sub

Replace "sheet2" with your sheet name, of course, and re-connect broken
lines due to Usenet concatenation (fourth line up).
 
The widths shoulb be a string data type:

UserForm1.Listbox1.ColumnWidths = "20; 40; 25"

Just add quote marks.


JLGWhiz said:
To manually adjust the columns of a listbox in the properties window, if
you have three columns:

ColumnWidth | 20, 40, 25

Would set column 1 to 20, col 2 to 40 and col 3 to 25



Roger on Excel said:
[Excel 2003]
I use listboxes to display tables of data.

Is there a way to adjust individual column widths such that one can make
the
columns fit - some of my columns need to be narrow and some wide.

Thus far I have only been able to make the first column width adjusted
(ColumnWidth Property) to what i want, but the others seem to be set to a
predefined default width.

Surely one can use code to adjust all the widths??

Roger
Click to expand...
Click to expand...
 
Back
Top