Auto adjusting a user form

[David Looney]

Hi,



Below is some code that populates a list box with the used range of a
worksheet and puts check boxes next to them.



How can I adjust this code to:

Show the first line as a header row (no check boxes)

Is it possible to auto adjust the row height or make the text wrap inside
the list box? Basically, some of the cells in the worksheet contain a lot of
text that wraps in the cell and I would like it to do the same inside the
list box.



Thanks





Private Sub UserForm_Initialize()

Dim ColCnt As Integer

Dim rng As Range

Dim cw As String

Dim c As Integer



ColCnt = ActiveSheet.UsedRange.Columns.Count

Set rng = ActiveSheet.UsedRange

With ListBox1

.ColumnCount = ColCnt

.RowSource = rng.Address

cw = ""

For c = 1 To .ColumnCount

cw = cw & rng.Columns(c).Width & ";"

Next c

.ColumnWidths = cw

.ListIndex = 0

End With

End Sub

 

[Jim Cone]

David,

The RowSource address for the list box must exclude the header row.
The ColumnHeads property for the list box must be set to True.
I don't know of anyway to adjust the height of the list box rows.
Regards,
Jim Cone
San Francisco, USA

'-----------------------
Private Sub UserForm_Initialize()
Dim ColCnt As Integer
Dim rng As Range
Dim cw As String
Dim c As Integer

ColCnt = ActiveSheet.UsedRange.Columns.Count
Set rng = ActiveSheet.UsedRange
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
With ListBox1
.ColumnCount = ColCnt
.RowSource = rng.Address(external:=True)
For c = 1 To .ColumnCount
cw = cw & rng.Columns(c).Width & ";"
Next c
.ColumnWidths = cw
.ListIndex = 0
End With
Set rng = Nothing
End Sub
'------------------------

"David Looney" <[email protected]>
wrote in message
Hi,
Below is some code that populates a list box with the used range of a
worksheet and puts check boxes next to them.
How can I adjust this code to:
Show the first line as a header row (no check boxes)
Is it possible to auto adjust the row height or make the text wrap inside
the list box? Basically, some of the cells in the worksheet contain a lot of
text that wraps in the cell and I would like it to do the same inside the
list box.
Thanks

Private Sub UserForm_Initialize()
Dim ColCnt As Integer
Dim rng As Range
Dim cw As String
Dim c As Integer
ColCnt = ActiveSheet.UsedRange.Columns.Count
Set rng = ActiveSheet.UsedRange
With ListBox1
.ColumnCount = ColCnt
.RowSource = rng.Address
cw = ""
For c = 1 To .ColumnCount
cw = cw & rng.Columns(c).Width & ";"
Next c
.ColumnWidths = cw
.ListIndex = 0
End With
End Sub