Excel

Multiline TextBox: Restrict accidental exit while DownArrow/UpArrow through the text

Ease of Use

Easy

Version tested with

2003 

Submitted by:

tstav

Description:

To exit the multiline TextBox and move to another textbox, you need to TAB/ShiftTAB. Arrows will be stopped at top/bottom lines of text. 

Discussion:

When TextBoxes have many lines of text, we usually move up and down using the Up/Down Arrows, in order to either edit or add text. But as we reach the top or bottom line intending to add some text, it often happens that we press one click too many and the cursor jumps to the next/previous textbox (if there is one). If you need to halt the cursor from exiting your multiline textbox, use the code I'm submitting (you don't need to use the ThisWorkbook code though, this is only for test purposes). 

Code:

instructions for use 

			
Option Explicit 
 'The following goes in the ThisWorkbook Module
Private Sub Workbook_Open() 
    Load UserForm1 
    UserForm1.Show 
End Sub 
 
 'The following goes in the Userform1 Module
 'This Userform contains the TextBox
Private Sub TextBox_KeyDown _ 
    (ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) 
     '''''''''''''''''''''''''''''''''''''''''''''''''''
     'If the TextBox contains more than one line,
     'beep when you reach the last line by DownArrow-ing,
     'and do not exit the textbox
     '''''''''''''''''''''''''''''''''''''''''''''''''''
    If KeyCode = vbKeyDown Then 
        With Me.TextBox 
            If .LineCount > 1 And _ 
            .CurLine = .LineCount - 1 Then 
                Beep 
                .SetFocus 
                .CurLine = .LineCount - 2 
            End If 
        End With 
    End If 
     ''''''''''''''''''''''''''''''''''''''''''''''''
     'If the TextBox contains more than one line,
     'beep when you reach the top line by UpArrow-ing,
     'and do not exit the textbox
     ''''''''''''''''''''''''''''''''''''''''''''''''
    If KeyCode = vbKeyUp Then 
        With Me.TextBox 
            If .LineCount > 1 And _ 
            .CurLine = 0 Then 
                Beep 
                .SetFocus 
                .CurLine = 1 
            End If 
        End With 
    End If 
End Sub

How to use:

  1. Choice No1:
  2. Download the TextBox.zip file I have submitted as an attachment.
  3. Unzip it and you will have the TextBox.xls file. Double click it to run.
  4. Choice No2:
  5. Create a new XLS file. Press Alt-F11 to get into the VBEditor.
  6. Copy the code to the specific modules I'm mentioning in my code's comments.
  7. Close the VBE.
  8. Save the file (call it whatever you like). Open it.
 

Test the code:

  1. Move the cursor up and down.
  2. When you reach the top or bottom of the middle textbox, you will hear a beep. The cursor will refuse to exit the textbox.
  3. Of course if you don't like the beeping, erase the 'beep' row from the code.
 

Sample File:

TextBox.zip 7.17KB 

Approved by mdmackillop


This entry has been viewed 183 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express