Some "basic" TextBox assistance

I have two questions to ask that relate specifically to TextBoxes. Neither of which I have been able to find a solution for, either by looking online or trial and error on my own UserForm.

1) How do I get a TextBox to respect multiple lines of text? At the moment when the 'Enter' key is pressed on the UserForm, the text transfers to the word document in one continuous line, not even respecting any spaces after a full stop.

I have Enterkey Behaviour=True
Multiline = True
Wordwrap = True

There are no other overriding lines of code. The only textbox that it is respecting the multi-line is a TextBox that is prefilled with two lines of text and a & Chr(11) & in between them.

This issue is just so annoying!

2) How can one position a TextBox (using code) relatively from a say a CommandButton?

The TextBox becomes visible when a CommandButton is pressed, but I would like to position it relatively to this. The reason for this is that this same TextBox is used with a number of CommandButtons going through the form.

Thanks!

Steve

1) Enterkey Behaviour=True means the Enter Key act like the Tab key. Try setting it to False. Ohterwise :dunno:

2)

Code:

---

Dim HasFocus As String

---

Code:

---

CommandButon1_Click)
  HasFocus = "CommandButton1"
  ShowTextBox Me.CommandButton1
  'More Code
End Sub

---

Code:

---

Sub ShowTextBox(Ctrl As MSForms.Control)
With Ctrl
      ctrlTop = Ctrl.Top
      ctrlLeft = Ctrl.Left
End With

With TextBox1
  .Top = CtrlTop +- ???
  .left = CtrlLeft +- ???
  .Text = TBText(Ctrl)
  .Visible = True
End With
End Sub

---

Code:

---

TextBox1_Click
  Me.TextBox1.Visible = False
  Me.Controls(HasFocus).SetFocus
  HasFocus  = ""
End Sub

---

That's the basics. I usually Use a Function to return any text needed

Code:

---

Function TBText(Ctrl As MSForms.Control) As String
  Select Case Ctrl.Name
      Case = "CommandButton1"
        TBText = "This Is Sample Text to Show in TExtBox"
  End Select
End function

---

Quote:

---

Originally Posted by HTSCF Fareha

1) How do I get a TextBox to respect multiple lines of text? At the moment when the 'Enter' key is pressed on the UserForm, the text transfers to the word document in one continuous line, not even respecting any spaces after a full stop.

I have Enterkey Behaviour=True
Multiline = True
Wordwrap = True

---

Shift-Enter

Quote:

---

Originally Posted by HTSCF Fareha

1) How do I get a TextBox to respect multiple lines of text? At the moment when the 'Enter' key is pressed on the UserForm, the text transfers to the word document in one continuous line, not even respecting any spaces after a full stop.

I have Enterkey Behaviour=True
Multiline = True
Wordwrap = True

---

If the useform text box in question has the above property settings then the enter key will insert a paragraph break in the text box. However if this text box is one you have referred to in previous postings it seems clear that you are processing it in various ways and therefore possible that these settings have been changed in code. Check your code that refers to the text box in question.

Setting Enterkey Behaviour=False will prevent the enter key being used in the text box and instead works as a tab to the next field, however typing Shift+Enter as suggested by Paul will insert a paragraph break regardless of the property setting.

Thanks to all for their input.

Focussing on point one, I've managed to find out why multi-line doesn't work. It was my content controls on the document itself. Changed the property of these and problem sorted!

Phew!

Time for a breather before starting point 2!

Okay, now for point 2.

SamT, I have used your code as below, but am getting a 'compile error, variable not defined' here

Code:

---

ctrlTop = Ctrl.Top

---

What are the values measured in when positioning the TextBox?

Code:

---

Option Explicit

Dim HasFocus  As String

Private Sub CommandButton1_Click()

    HasFocus = "CommandButton1"
    ShowTextBox Me.CommandButton1
   
    TextBox10.Height = 75
   
End Sub

Private Sub TextBox10_Click()
    Me.TextBox10.Visible = False
    Me.Controls(HasFocus).SetFocus
    HasFocus = ""
End Sub

Sub ShowTextBox(Ctrl As MSForms.Control)
    With Ctrl
        ctrlTop = Ctrl.Top
        ctrlLeft = Ctrl.Left
    End With
   
    With TextBox10
        .Top = ctrlTop + 1
        .Left = ctrlLeft + 2
        .Text = TBText(Ctrl)
        .Visible = True
    End With
End Sub

Function TBText(Ctrl As MSForms.Control) As String
    Select Case Ctrl.Name
        Case Is = "CommandButton1"
            TBText = "This Is Sample Text to Show in TextBox"
        Case Is = "CommandButton2"
            TBText = "This Is Sample Text to Show in TextBox"
        Case Is = "CommandButton3"
            TBText = "This Is Sample Text to Show in TextBox"
        Case Is = "CommandButton4"
            TBText = "This Is Sample Text to Show in TextBox"
    End Select
End Function

---

Thanks!
Steve

Code:

---

Private ctrlTop As Long, ctrlLeft As Long

---

have not been declared at the top of the userform code module
The measurements are in Twips
If you want the textbox alongside the command button then set ctrlLeft to something like

Code:

---

ctrlLeft = Ctrl.Left + Ctrl.Width + 4

---

Thanks Graham this got rid of the error, but the TextBox is still insisting on placing itself according to the UserForm's initial placement.

Code:

---

Option Explicit

Dim oRng      As Range
Dim HasFocus  As String
Private ctrlTop As Long, ctrlLeft As Long

Private Sub CancelBut_Click()
    Tag = 0
    Hide
End Sub

Private Sub UserForm_Initialize()
    ' Set Form Position
    StartUpPosition = 0
    Left = Application.Left + (Application.Width - Width) * 0.5
    Top = Application.Top + (Application.Height - Height) * 0.5
   
    ' Set default value
    TextBox2.Text = "None."
   
lbl_Exit:
    Exit Sub
End Sub

' Enter button
Private Sub EnterBut_Click()
    If TextBox1.Text = "" Then
        MsgBox "Select reason", vbExclamation + vbOKOnly, "Triage Hub"
        TextBox1.SetFocus
        GoTo lbl_Exit
    End If
    If TextBox3.Text = "" Then
        MsgBox "Enter research", vbExclamation + vbOKOnly, "Triage Hub"
        TextBox3.SetFocus
        GoTo lbl_Exit
    End If
   
    Tag = 1
    Hide
lbl_Exit:
    Exit Sub
End Sub

Private Sub OptionButton1_Change()
    If OptionButton1.Value = True Then
        TextBox2.Visible = False
        TextBox2.Text = "None."
    Else
        TextBox2.Visible = True
        TextBox2.Text = ""
    End If
End Sub

Private Sub CommandButton1_Click()

    HasFocus = "CommandButton1"
    ShowTextBox Me.CommandButton1
    TextBox10.Height = 75
 
End Sub

Private Sub TextBox10_Click()

    Me.TextBox10.Visible = False
    Me.Controls(HasFocus).SetFocus
    HasFocus = ""
End Sub

Sub ShowTextBox(Ctrl As MSForms.Control)
    With Ctrl
        ctrlTop = Ctrl.Top
        ctrlLeft = Ctrl.Left
    End With
   
    With TextBox10
        ctrlLeft = Ctrl.Left + Ctrl.Width + 4
        .Text = TBText(Ctrl)
        .Visible = True
    End With
End Sub

Function TBText(Ctrl As MSForms.Control) As String
    Select Case Ctrl.Name
        Case Is = "CommandButton1"
            TBText = "This Is Sample Text to Show in TextBox"
        Case Is = "CommandButton2"
            TBText = "This Is Sample Text to Show in TextBox"
        Case Is = "CommandButton3"
            TBText = "This Is Sample Text to Show in TextBox"
        Case Is = "CommandButton4"
            TBText = "This Is Sample Text to Show in TextBox"
    End Select
End Function

---

Steve,
233 posts and you still don't know about declaring variables?

Code:

---

Sub ShowTextBox(Ctrl As MSForms.Control)
Dim ctrlLeft as Single
Dim ctrlTop as Single

    ctrlTop = Ctrl.Top
    ctrlLeft = Ctrl.Left
   
    With TextBox10
'Place the AlertBox 1 Point below the top edge of the Ctrl, 2 Points to the Right of the left edge of the control
        .Top = ctrlTop + 1
        .Left = ctrlLeft + 2
        .Text = TBText(Ctrl)
        .Visible = True
    End With
End Sub

---

In my version of Office, Top and Left, (as well as Height and width,) are measured in Points and are positions relative to the Top Left corner of the Container. The Container is either, the UserForm or a Frame. Note that for a UserForm the actual location is calculated from the Form's StartUoPosition Property and the Width and Height of the UserForm. The UsewrForm's Top and Left Property values are zero for calculating Control locations.

Many thanks, SamT, this has everything working.

You are correct about variables, but it's knowing exactly which one to use for each case. Some are obvious like Integer, String, Boolean and Date, but you have used Single for this. After now looking up what this is, why would you not use Integer?

Steve

"Single" is the native type for UserForm's, and their Controls', Top, Left, Width, and Height Properties. When VBA converts one Type to another, it takes CPU cycles

• Byte variables are stored as single, unsigned, 8-bit (1-byte) numbers ranging in value from 0–255.
• Integer variables are stored as 16-bit (2-byte) numbers ranging in value from -32,768 to 32,767. The type-declaration character for Integer is the percent sign (%).
• Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647. The type-declaration character for Long is the ampersand (&).
• Currency variables are stored as 64-bit (8-byte) numbers in an integer format, scaled by 10,000 to give a fixed-point number with 15 digits to the left of the decimal point and 4 digits to the right. This representation provides a range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807. The type-declaration character for Currency is the at sign (@).
• Single (single-precision floating-point) variables are stored as IEEE 32-bit (4-byte) floating-point numbers, ranging in value from -3.402823E38 to -1.401298E-45 for negative values and from 1.401298E-45 to 3.402823E38 for positive values. The type-declaration character for Single is the exclamation point (!).
• Double (double-precision floating-point) variables are stored as IEEE 64-bit (8-byte) floating-point numbers ranging in value from -1.79769313486231E308 to -4.94065645841247E-324 for negative values and from 4.94065645841247E-324 to 1.79769313486232E308 for positive values. The type-declaration character for Double is the number sign (#).
• Decimal variables are stored as 96-bit (12-byte) signed integers scaled by a variable power of 10. The power of 10 scaling factor specifies the number of digits to the right of the decimal point, and ranges from 0 to 28. With a scale of 0 (no decimal places), the largest possible value is +/-79,228,162,514,264,337,593,543,950,335. With a 28 decimal places, the largest value is +/-7.9228162514264337593543950335 and the smallest, non-zero value is +/-0.0000000000000000000000000001.
  
• Note At this time the Decimal data type can only be used within a Variant, that is, you cannot declare a variable to be of type Decimal. You can, however, create a Variant whose subtype is Decimal using the CDec function.

32bit Longs are the integer equivalent of 32bit decimal Singles

There is no 16bit Integer equivalent decimal Type


Newer versions of Office have Help online, (accessible by pressing F1 while the cursor is on a word,) but I find Versions 97 thru 2003 have builtin Help that is much faster and doesn't need an internet connection. These versions (OEM) are available on ebay (for example) for around $10US and the VBA in them is ~98% identical. I, myself, own 3 or 4 Office XP CDs. I will dual install XP with Version 2007 or 2013 just for the fast Help.

Many thanks SamT for this explanation of variables. I have made a copy of this to keep as a handy reference.

Steve