Consulting

Results 1 to 12 of 12

Thread: Some "basic" TextBox assistance

  1. #1
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location

    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

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    1) Enterkey Behaviour=True means the Enter Key act like the Tab key. Try setting it to False. Ohterwise :

    2)
    Dim HasFocus As String
    CommandButon1_Click)
       HasFocus = "CommandButton1"
       ShowTextBox Me.CommandButton1
       'More Code
    End Sub
    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
    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
    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
    Last edited by SamT; 04-01-2021 at 01:32 PM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by HTSCF Fareha View Post
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Quote Originally Posted by HTSCF Fareha View Post
    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.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  5. #5
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    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!

  6. #6
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Okay, now for point 2.

    SamT, I have used your code as below, but am getting a 'compile error, variable not defined' here
    ctrlTop = Ctrl.Top
    What are the values measured in when positioning the TextBox?


    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

  7. #7
    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
    ctrlLeft = Ctrl.Left + Ctrl.Width + 4
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  8. #8
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Thanks Graham this got rid of the error, but the TextBox is still insisting on placing itself according to the UserForm's initial placement.

    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

  9. #9
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Steve,
    233 posts and you still don't know about declaring variables?
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  10. #10
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    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

  11. #11
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    "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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  12. #12
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Many thanks SamT for this explanation of variables. I have made a copy of this to keep as a handy reference.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •