Textbox .ColumnWidths without trial and error?

[Paul_Hossler]

Is there a way to calculate the max width required for a Textbox .ColumnWidths?

Right now I have to fiddle with the .ColumnWidths settings manually to adjust them

Example:

100pt;100pt;100pt;100pt; -- col 1 too large
75pt;100pt;100pt;100pt; -- col 1 closer
50pt;100pt;100pt;100pt; -- col 1 too much
60pt;100pt;100pt;100pt; -- col 1 good enough, col2 too small
60pt;120pt;100pt;100pt; -- col2 closer
60pt;130pt;100pt;100pt; -- col2 OK

etc.

I can find the max length of elements in each column and if there was a formula or algorithm, I'd construct .ColumnWidths at display time

[Aflatoon]

It'll only be easy if you're using a fixed-width font.

[mikerickson]

I'm not familiar with a Text box that has a column widths property. (Perhaps its ActiveX on a worksheet, something I don't have)

Perhaps
Set the TextBox to single column, set it to AutoSize=True, SelectionMargin=False, put the longest column 1 entry in the box. That will give you the width for column 1.
Do that to get the column widths for each column.
Re-set it to multi-column and apply the widths you found.

[Paul_Hossler]

Sorry - I wasn't very clear and left off important information

I was asking about user form textbox controls. These will receive multiple columns of data of varying widths and it's a pain to have to manually fiddle the column widths for each textbox.

I can find the longest (in terms of character count) in each data column, and I was seeing if there's a way to avoid manually playing around with the widths

15450

[mikerickson]

The property window in your image is for a listbox.

You could call this in the initialize event. (The "longest possible string in column i" could be refined)


Private Sub UserForm_Initialize()
Call AdjustColumnWidths(Me.ListBox1)
End Sub

Sub AdjustColumnWidths(aListBox As MSForms.ListBox)
Dim tempBox As MSForms.TextBox
Dim colWidthString As String
Dim i As Long

Set tempBox = aListBox.Parent.Controls.Add("forms.TextBox.1")
With tempBox
With .Font
.Bold = aListBox.Font.Bold
.Charset = aListBox.Font.Charset
.Italic = aListBox.Font.Italic
.Name = aListBox.Font.Name
.Size = aListBox.Font.Size
End With
.AutoSize = True
.SelectionMargin = False
.MultiLine = False
.WordWrap = False
.Visible = True
End With

For i = 1 To aListBox.ColumnCount
tempBox.Width = 300
tempBox.Text = "Longest Possible String in column " & i
colWidthString = colWidthString & ";" & tempBox.Width
Next i

tempBox.Parent.Controls.Remove tempBox.Name
colWidthString = Mid(colWidthString, 2)
aListBox.ColumnWidths = colWidthString
Set tempBox = nothing
End Sub

[Paul_Hossler]

The property window in your image is for a listbox.

Mike - 2
Paul - 0


I'll have to step through your example but autosizing a textbox to get the col width seems like a clever idea

[SamT]

This will get you close. Adjust M after a bit of Experimentation.

Const M as Double = .707 'An Em is as tall as it is wide, in Points
Dim W1, W2, W3, W4 'Variants. Will be Doubles and Strings.
Dim L1, L2, L3, L4 'Variants: Arrays
Dim i As Long
Dim S As Currency

S = Listbox1.Font.Size

With ListBox1
L1 = .Column(1)
L2 = .Column(2)
Etc
End With

For i = Lbound( L1) to UBound(L1)
IF Len(L1(i)) > W1 Then W1 = Len(L1)(i))
IF Len(L2(i)) > W2 Then W2 = Len(L2)(i))
Etc
Next i

W1 = Ctr(W1 * M * S)
W2 = CStr(W2 * M* S)
Etc

ListBox1.ColumnWidths = W1 & ";" & W2 & ";" & W3 & ";" W4


When you get a real good idea of the value of M IRL, you can write this up as an Article. Also see what good it does

[man92]

You can use a subform in query mode with this code:

'Automatic width
For i = 0 To Me.SubFormName.Controls.Count - 1
Me.SubFormName.Controls(i).ColumnWidth = -2
Next

'Automatic height
Me.SubFormName.Form.RowHeight = -1

[Jan Karel Pieterse]

See: http://www.jkp-ads.com/Articles/autosizelistbox.asp

[Paul_Hossler]

@All -- thanks for the ideas and examples

I never thought about an autosizing a textbox or a label to find how long a column will be