AutoSizing Text Fields on a pop-up form ... (1 Viewer)

[Marshall Brooks]

Basically, I have the same question as this thread - https://www.access-programmers.co.uk/forums/threads/auto-size-width-or-label-or-text-box.53796/ - Except in my case, I want the textbox or label to grow vertically and stay fixed width.

I somewhat asked here also: https://www.access-programmers.co.u...-set-userform-combobox-from-sql.328825/page-4 - in Reply #77.

To explain: I have a USERFORM for an InputBox that will dynamically size like this:

With InputBox1
    .caption = "Verify Title:"
    .Label1.caption = "Please verify database title matches official title." & vbCrLf & vbCrLf & "Click OK when complete."
    .Label1.Font.Size = 10
    .Label1.AutoSize = False
    .Label1.AutoSize = True
    .Label1.Width = 228
    .ComboBox1.Visible = False
    .TextBox1.Visible = True
    .TextBox1.top = IIf((.Label1.Height + 10) > 50, .Label1.Height + 10, 50) ' 50 or .Label1.Height+10, whichever is greater.
    .TextBox1.value = "Content of TITLE Field" & ""
    ' Complicated AutoSize Off-On-Off arrangement is required to allow form to autoheight to text box, but not expand if additional rows are added.
    .TextBox1.AutoSize = False
    .TextBox1.AutoSize = True
    .TextBox1.Width = 300
    .TextBox1.AutoSize = False
    .TextBox1.ScrollBars = fmScrollBarsVertical
    .TextBox1.SetFocus
    .TextBox1.SelStart = 0
    .Height = .Label1.Height + .TextBox1.Height + 50
    Dim sngLeft As Single
    Dim sngTop As Single
    Call ReturnPosition_CenterScreen(.Height, .Width, sngLeft, sngTop)
    .left = sngLeft
    .top = sngTop
    .Show
End With

i.e, the label width is 228 and the height adjusts to whatever it needs to be. The textbox top is at 50 or ten more than the end of the label and the width is 300 and the entire userform is sized to 50 more than the height of the label and text box.

I am trying to do the same thing with an Access Pop-Up form and I ran into the following:

DoCmd.OpenForm "frmModelessBox"
With Forms![frmModelessBox]
     .caption = "Message1"
     .Prompt.value = "Line1" & vbCrLf & "Line2" & vbCrLf & "Line3" & vbCrLf & "Line4"
     .Prompt.top = 200
     .Prompt.SizeToFit
     .Prompt.Width = 5000
End With

I tried with .Prompt as a label and with .Prompt as a textbox.

.Prompt.AutoSize is not recognized
.Prompt.SizeToFit seemed promising, but I get an error that it is only available in design view.
.Prompt.CanGrow didn't seem to be recognized, but from the first link, it only works when printing, anyway ...

 

[CJ_London]

it may be you can use the wizhook twipsFromFont function to determine the height of the font which can then be multiplied by the number of lines to set the control height. @isladogs has an article here

Wizhook

A detailed reference guide to the hidden, undocumented but very useful Access WizHook object and its members

isladogs.co.uk

 

[Marshall Brooks]

Indeed! Amazing how much info @isladogs has available.

The issue is that I don't see how it would account for word wrap.

The prompt value in my example was just for testing. I want a fixed width of 5000 (ermmm - units).

If I feed it "Here's some very long example text for my example" I'm not sure how WizHook would know to move that to a new line following example, but maybe that could be calculated somehow.

 

[Marshall Brooks]

I see issues with it, but I somewhat see how it could be done. I would like the code to account for both word wrap and vbCrLf.

So I think it would be something like - starting with:

"Here is my Example Text" & vbCRLF & "Line 2" & vbcrlf & "Line 3"

Count the number of vbcrlf and add one - there will be AT Least that many lines of text.

Replace " & vbCrLF & " with something unique - maybe ~

Do a split on that to save each line to an array variable.

Parse the array and divide by 5000 and round up and subtract 1 (If the array item is narrower than 5000, return 0 if it is 7000 return 1 for one added line, if it is 14000, return 2 for 2 added lines).

Add the above to get the total number of lines.

Figure the height from the result.

 

[CJ_London]

The issue is that I don't see how it would account for word wrap.

it can work out the number of twips required for an entire string. Divide that by the width of your control will give you the number of lines. OK, not perfect since it wraps at the end of a word rather than the middle.

Or split the string on a space and iterate through each word, adding the twips as you go. If the added twips exceeds the control width, add 1 for the row and use that last words number of twips to start adding again from zero

 

[Marshall Brooks]

I think it's going to be easier to modify a userform, but I haven't gotten that working either. I'm going to post in the second thread above. Great suggestions, though!!!

 

[isladogs]

I'm not necessarily suggesting its use but Wizhook can also calculate the total width needed for a text string. If you have a textbox with fixed width, you could then determine the number of lines needed and hence the control height.

 

[CJ_London]

I think it's going to be easier to modify a userform

You give up too easily

Here is a simple example to demonstrate using wizhook twipsFromFont to dynamically change the height of a control to match the contents.

There is a table with 3 records, each with different amounts of text, use the form navigation button to move between records and change the control settings in the form header.

It is not 100 percent perfect - for example changing the font size to 12 results in the second record's bottom line not being visible. I suspect this is to do with internal calculations and the conversion of character heights rounding down rather than up. But can easily be fixed by just adding an extra line - or use a different font size/font. I didn't do it because I wanted to illustrate the issue.

 

[Marshall Brooks]

Thank you!!! That looks like it should work perfectly for me. Very much appreciated!!!

 

[Marshall Brooks]

@CJ_London - Getting there. I think your code is sizing the control properly. Now I need to size the form to fit the control. I put your resizeControl function in the form's code rather than a separate module.

What I need to do is size the control to fit the text and then size the form to fit the control.

I have the following code:
With Forms![frmModelessAutocloseBox]
.caption = "Message1"
.Prompt.value = "Line1" & vbCrLf & "Line2" & vbCrLf & "Line3" & vbCrLf & "Line4" & vbCrLf & "Line5" & vbCrLf & "Line6" & vbCrLf & "Line7" & vbCrLf & "Line8" & vbCrLf & "Line9"
.Prompt.top = 100
' .Prompt.SizeToFit
.Prompt.Width = 5000
.resizeControl .Prompt
.WindowHeight = .Prompt.Height + 250
Dim sngLeft As Single
Dim sngTop As Single
Call ReturnPosition_CenterScreen(.WindowHeight / 20, .WindowWidth / 20, sngLeft, sngTop)
' Output is in points and needs to be in Twips
sngLeft = sngLeft * 20
sngTop = sngTop * 20
On Error Resume Next
DoCmd.MoveSize , sngTop
On Error GoTo 0
.Show
End With

On the red line above, I am getting RT Error 2135 "This property is read-only and can't be set."

I have the userform code working so I can use that instead if the pop-up form can't be made to work.