I have a 3 column worksheet (chart of accounts - Code, Detail, Group). Each
column is of a different width. I can load these 3 columns onto a ListBox but
some of the text is not legible as all columns are of the same size in the
ListBox. Need some code to adjust column widths in the LIstBox so that all of
the text in each column is legible.

Many thanks/sgl

You can adjust the ColumnWidths right in the Properties box if the widths
are fixed, or with code at runtime:

ListBox1.ColumnWidths = "30;40;50"

--
Jim
"sgl" wrote in message
...
|I have a 3 column worksheet (chart of accounts - Code, Detail, Group). Each
| column is of a different width. I can load these 3 columns onto a ListBox
but
| some of the text is not legible as all columns are of the same size in the
| ListBox. Need some code to adjust column widths in the LIstBox so that all
of
| the text in each column is legible.
|
| Many thanks/sgl

Jim thanks for your reply. This is the code that I have. I have tried what
you suggested but cannot seem to get it to work. What am I doing wrong?

Private Sub ListBox1_Change()

Dim SourceRange as Range

Dim Val1 As String, Val2 As String, Val3 As String

Set SourceRange = Range(ListBox1.RowSource)

ListBox1.ColumnWidths = "10;65;10"

Val1 = ListBox1.Value
Val2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize(1, 1).Value
Val3 = SourceRange.Offset(ListBox1.ListIndex, 2).Resize(1, 1).Value

' Label1.Caption = Val1 & " " & Val2 & " " & Val3

End Sub

Thanks/sgl

"Jim Rech" wrote:

You can adjust the ColumnWidths right in the Properties box if the widths
are fixed, or with code at runtime:

ListBox1.ColumnWidths = "30;40;50"

--
Jim
"sgl" wrote in message
...
|I have a 3 column worksheet (chart of accounts - Code, Detail, Group). Each
| column is of a different width. I can load these 3 columns onto a ListBox
but
| some of the text is not legible as all columns are of the same size in the
| ListBox. Need some code to adjust column widths in the LIstBox so that all
of
| the text in each column is legible.
|
| Many thanks/sgl

It doesn't look as if you're allowed to change column widths during the list
box change event. In any case that's a pretty strange place to do it
anyway.

Since the widths are always the same apparently, you should change the
column width property manually in developer mode:
-Click on the list box to select it.
-Press F4 to open Properties.
-Enter "10;65;10" (no quotes) for the ColumnWidths property.

and you're done. Kill that odd line of code.

Otherwise set the column widths in the Initialize event:

Private Sub UserForm_Initialize()
ListBox1.ColumnWidths = "10;65;10"
End Sub

--
Jim
"sgl" wrote in message
...
| Jim thanks for your reply. This is the code that I have. I have tried what
| you suggested but cannot seem to get it to work. What am I doing wrong?
|
| Private Sub ListBox1_Change()
|
| Dim SourceRange as Range
|
| Dim Val1 As String, Val2 As String, Val3 As String
|
| Set SourceRange = Range(ListBox1.RowSource)
|
| ListBox1.ColumnWidths = "10;65;10"
|
| Val1 = ListBox1.Value
| Val2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize(1, 1).Value
| Val3 = SourceRange.Offset(ListBox1.ListIndex, 2).Resize(1, 1).Value
|
| ' Label1.Caption = Val1 & " " & Val2 & " " & Val3
|
| End Sub
|
| Thanks/sgl
|
| "Jim Rech" wrote:
|
| You can adjust the ColumnWidths right in the Properties box if the
widths
| are fixed, or with code at runtime:
|
| ListBox1.ColumnWidths = "30;40;50"
|
| --
| Jim
| "sgl" wrote in message
| ...
| |I have a 3 column worksheet (chart of accounts - Code, Detail, Group).
Each
| | column is of a different width. I can load these 3 columns onto a
ListBox
| but
| | some of the text is not legible as all columns are of the same size in
the
| | ListBox. Need some code to adjust column widths in the LIstBox so that
all
| of
| | the text in each column is legible.
| |
| | Many thanks/sgl
|
|
|

Jim thanks a lot that worked. Did it manually

"Jim Rech" wrote:

It doesn't look as if you're allowed to change column widths during the list
box change event. In any case that's a pretty strange place to do it
anyway.

Since the widths are always the same apparently, you should change the
column width property manually in developer mode:
-Click on the list box to select it.
-Press F4 to open Properties.
-Enter "10;65;10" (no quotes) for the ColumnWidths property.

and you're done. Kill that odd line of code.

Otherwise set the column widths in the Initialize event:

Private Sub UserForm_Initialize()
ListBox1.ColumnWidths = "10;65;10"
End Sub

--
Jim
"sgl" wrote in message
...
| Jim thanks for your reply. This is the code that I have. I have tried what
| you suggested but cannot seem to get it to work. What am I doing wrong?
|
| Private Sub ListBox1_Change()
|
| Dim SourceRange as Range
|
| Dim Val1 As String, Val2 As String, Val3 As String
|
| Set SourceRange = Range(ListBox1.RowSource)
|
| ListBox1.ColumnWidths = "10;65;10"
|
| Val1 = ListBox1.Value
| Val2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize(1, 1).Value
| Val3 = SourceRange.Offset(ListBox1.ListIndex, 2).Resize(1, 1).Value
|
| ' Label1.Caption = Val1 & " " & Val2 & " " & Val3
|
| End Sub
|
| Thanks/sgl
|
| "Jim Rech" wrote:
|
| You can adjust the ColumnWidths right in the Properties box if the
widths
| are fixed, or with code at runtime:
|
| ListBox1.ColumnWidths = "30;40;50"
|
| --
| Jim
| "sgl" wrote in message
| ...
| |I have a 3 column worksheet (chart of accounts - Code, Detail, Group).
Each
| | column is of a different width. I can load these 3 columns onto a
ListBox
| but
| | some of the text is not legible as all columns are of the same size in
the
| | ListBox. Need some code to adjust column widths in the LIstBox so that
all
| of
| | the text in each column is legible.
| |
| | Many thanks/sgl
|
|
|