none
Entering text in cells RRS feed

  • Question

  • Hello,

     

    On my Excel Sheet, the user enters some information on one area of the sheet and if they do not select the next cell when the first cell gets full, some of the text is not visible when you print the form.

     

    I need to figure out if there is a way to make the sheet automatically select the next cell down when the first one gets full, like it would in a Word document, or if you would wrap the text in the cell.

     

    I have tried using a textbox activex control... but I don't know how to do it except by counting the characters, so it would split the text in the middle of a word if you didn't watch what you were doing. Plus it was cumbersome because you had to select the cell you wanted the text in, then select the textbox and begin typing.

     

    Is there any way to do this??

     

    Thanks in advance!

    Friday, January 27, 2017 7:50 PM

Answers

  • This solution is to move the text to the following cells without Merging cells.

    Try the following event code. It runs after the user has completed entering the text in the cell. Note that ColumnWidth is actually the approximate number of characters that will fit in the cell. It is not exact science. I understand that the value is from an algorithm that calculates based on the average width of different characters(eg M is wider than I) but I think that it is as close as you are going to get. If you experience some of the cells having say one more character than will fit in the cell then try lngMaxChars = Target.ColumnWidth - 1 and make the maximum character one less than columnWidth.

    You might know this but just in case. Right click the worksheet tab name and select View code to open the VBA editor and then copy and paste the code into the worksheets module.The code automatically runs when the user has completed entering the text in the cell and then presses Enter or moves to another cell.

    Feel free to get back to me with any problems or if you would rather have the Merge Cells option.

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim lngMaxChars          'Maximum permitted characters in cell
        Dim arrSplit As Variant
        Dim arrRows() As Variant
        Dim i As Long
        Dim j As Long
        Dim r As Long
       
        On Error GoTo ReEnableEvents
        Application.EnableEvents = False
       
        If Target.Column = 1 And Target.Cells.Count = 1 Then
            If Target.Value <> vbNullString Then      'Cannot be included in previous line with And
               
                lngMaxChars = Target.ColumnWidth         'Set to approx number of characters for column width
               
                arrSplit = Split(Target.Value, " ")
                j = 1       'Initialize to 1
                ReDim arrRows(1 To j)   'Initialize to one element otherwise Redim Preserve errors
               
                For i = LBound(arrSplit) To UBound(arrSplit)
                    If Len(arrRows(j) & arrSplit(i)) <= lngMaxChars Then
                        arrRows(j) = arrRows(j) & arrSplit(i) & " "
                    Else
                        j = j + 1
                        ReDim Preserve arrRows(1 To j)
                        arrRows(j) = arrSplit(i) & " "
                    End If
                Next i
               
                j = 0
                For r = LBound(arrRows) To UBound(arrRows)
                    Target.Offset(j, 0) = Trim(arrRows(r))  'Removes last space inserted with above code
                    j = j + 1
                Next r
            End If
        End If
       
    ReEnableEvents:
        If Err.Number <> 0 Then
            MsgBox "Error occurred in Private Sub Worksheet_Change"
        End If
       
        Application.EnableEvents = True
       
    End Sub


    Regards, OssieMac

    • Proposed as answer by Chenchen LiModerator Tuesday, January 31, 2017 7:36 AM
    • Marked as answer by iamrick Tuesday, January 31, 2017 3:16 PM
    Tuesday, January 31, 2017 12:17 AM

All replies

  • Why not set all of the cells of concern to wrap text and the row height will simply expand.

    Also, don't know what else you are doing in the project but are you using the best software for the project. Maybe a table in Word will be better.


    Regards, OssieMac

    Saturday, January 28, 2017 8:38 AM
  • Hi,

    >>if there is a way to make the sheet automatically select the next cell down when the first one gets full

    We are unable to catch the event when typing in cells, so it is impossible to count how many characters in the cell when you are typing. In my opinion, we could handle worksheet_change event or selectionchange event to set it into wrap text or edit row height after inputting. Or you could check it characters count and then split it if it is too long.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 30, 2017 6:40 AM
    Moderator
  • Hi, 

    Why not set all of the cells of concern to wrap text and the row height will simply expand.

    Also, don't know what else you are doing in the project but are you using the best software for the project. Maybe a table in Word will be better.

    This is a form, and it needs to all fit on one page, so expanding the row height is not an option, also we need separate lines to correspond with the cells that are in the same row.

    Excel works best for this because of how the rest of the sheet works.

    Monday, January 30, 2017 4:50 PM
  • Hi,

    I can count characters in the cell by using a LEN formula on the sheet itself and grabbing that value with the VBA code.

    Splitting it if the character count is too long would work, but the problem I ran into, was that it would split in the middle of a word where it made no sense. :(

    Any other ideas?

    Thanks!

    Monday, January 30, 2017 4:54 PM
  • I believe that I can do this with VBA code. However, you have indicated a preference to use additional cells below the initial cell to accommodate the text. That that can be done because I can code to break the text only at the spaces.

    However, once I have calculated how many rows are required for the text, I can merge the initial cell with the required number of rows below the initial cell and wrap the text. Will this be a better option because then the entire text is really in one cell that is merged with the cells below.

    I will commence work on this while I await your answer because the code to split the text at the spaces and calculate the number of required rows is the same for both scenarios.


    Regards, OssieMac

    Monday, January 30, 2017 11:01 PM
  • This solution is to move the text to the following cells without Merging cells.

    Try the following event code. It runs after the user has completed entering the text in the cell. Note that ColumnWidth is actually the approximate number of characters that will fit in the cell. It is not exact science. I understand that the value is from an algorithm that calculates based on the average width of different characters(eg M is wider than I) but I think that it is as close as you are going to get. If you experience some of the cells having say one more character than will fit in the cell then try lngMaxChars = Target.ColumnWidth - 1 and make the maximum character one less than columnWidth.

    You might know this but just in case. Right click the worksheet tab name and select View code to open the VBA editor and then copy and paste the code into the worksheets module.The code automatically runs when the user has completed entering the text in the cell and then presses Enter or moves to another cell.

    Feel free to get back to me with any problems or if you would rather have the Merge Cells option.

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim lngMaxChars          'Maximum permitted characters in cell
        Dim arrSplit As Variant
        Dim arrRows() As Variant
        Dim i As Long
        Dim j As Long
        Dim r As Long
       
        On Error GoTo ReEnableEvents
        Application.EnableEvents = False
       
        If Target.Column = 1 And Target.Cells.Count = 1 Then
            If Target.Value <> vbNullString Then      'Cannot be included in previous line with And
               
                lngMaxChars = Target.ColumnWidth         'Set to approx number of characters for column width
               
                arrSplit = Split(Target.Value, " ")
                j = 1       'Initialize to 1
                ReDim arrRows(1 To j)   'Initialize to one element otherwise Redim Preserve errors
               
                For i = LBound(arrSplit) To UBound(arrSplit)
                    If Len(arrRows(j) & arrSplit(i)) <= lngMaxChars Then
                        arrRows(j) = arrRows(j) & arrSplit(i) & " "
                    Else
                        j = j + 1
                        ReDim Preserve arrRows(1 To j)
                        arrRows(j) = arrSplit(i) & " "
                    End If
                Next i
               
                j = 0
                For r = LBound(arrRows) To UBound(arrRows)
                    Target.Offset(j, 0) = Trim(arrRows(r))  'Removes last space inserted with above code
                    j = j + 1
                Next r
            End If
        End If
       
    ReEnableEvents:
        If Err.Number <> 0 Then
            MsgBox "Error occurred in Private Sub Worksheet_Change"
        End If
       
        Application.EnableEvents = True
       
    End Sub


    Regards, OssieMac

    • Proposed as answer by Chenchen LiModerator Tuesday, January 31, 2017 7:36 AM
    • Marked as answer by iamrick Tuesday, January 31, 2017 3:16 PM
    Tuesday, January 31, 2017 12:17 AM
  • I previously indicated that ColumnWidth is not an exact science and it is messy to determine exactly how many characters will fit in a given column width. It is possible to copy the initial string to a cell out of the way of the main data and AutoFit the column and then read its ColumnWidth and then mathematically determine how may characters should fit in your column but even so it will not necessarily return the exact number.

    If the following line of code does not work for you then you could try setting the number of characters directly in lieu of using ColumnWidth like the following and then experiment to see what number of characters appears to work the best for the column width you have.

    lngMaxChars = 21


    Regards, OssieMac

    Tuesday, January 31, 2017 4:17 AM
  • This is what I was looking for. Thanks! See code below that I modified for my needs. For my purposes it worked better to state the number of characters as opposed to using the column width.

    Private Sub Worksheet_Change(ByVal Target As Range)

        If TextBreakToggleButton = True Then
            Exit Sub
        End If
        
        Dim lngMaxChars          'Maximum permitted characters in cell
        Dim arrSplit As Variant
        Dim arrRows() As Variant
        Dim i As Long
        Dim j As Long
        Dim r As Long
       
        On Error GoTo ReEnableEvents
        Application.EnableEvents = False
       
        If Target.Column = 2 And Target.Cells.Count = 1 And Target.Row > 47 And Target.Row < 57 Then
            lngMaxChars = 75 'Target.ColumnWidth         'Set to approx number of characters for column width
        Else
            If Target.Column = 1 And Target.Cells.Count = 1 And Target.Row > 59 And Target.Row < 69 Then
                lngMaxChars = 97 'Target.ColumnWidth         'Set to approx number of characters for column width
            Else
                GoTo ReEnableEvents
            End If
        End If
            If Target.Value <> vbNullString Then      'Cannot be included in previous line with And
               
                arrSplit = Split(Target.Value, " ")
                j = 1       'Initialize to 1
                ReDim arrRows(1 To j)   'Initialize to one element otherwise Redim Preserve errors
               
                For i = LBound(arrSplit) To UBound(arrSplit)
                    If Len(arrRows(j) & arrSplit(i)) <= lngMaxChars Then
                        arrRows(j) = arrRows(j) & arrSplit(i) & " "
                    Else
                        j = j + 1
                        ReDim Preserve arrRows(1 To j)
                        arrRows(j) = arrSplit(i) & " "
                    End If
                Next i
               
                j = 0
                For r = LBound(arrRows) To UBound(arrRows)
                    Target.Offset(j, 0) = Trim(arrRows(r))  'Removes last space inserted with above code
                    j = j + 1
                Next r
                Target.Offset(j, 0).Select
            End If
            MsgBox "Code ran. lngMaxChars is " & lngMaxChars
        'End If
    ReEnableEvents:
        If Err.Number <> 0 Then
            MsgBox "Error occurred in Private Sub Worksheet_Change"
        End If
       
        Application.EnableEvents = True
       
    End Sub

    Tuesday, January 31, 2017 3:16 PM