Enter multiple lines of text and auto-fit row height

judgejustin

Board Regular
Joined
Mar 3, 2014
Messages
150
I need to allow users to enter multiple lines of information into a cell. A cell which I have merged across 4 cells in a row, mostly for aesthetic reasons. There are several things I need the user to be able to do. Keep in mind these will be a wide range of users and they do not have real excel experience. This is a form style set up for them to enter data and have it transfer.

1. Enter information and drop down a line (line break) while staying in the same cell to enter more information. (I will never get them to do Alt + Enter, so I need an easier way.)
2. Auto Size the row height in this cell dependent on how many lines they enter. (Some may need one line some may need 10 or more.)
3. Just for your knowledge the data will be copied over to a couple of different places so I don't think a text box is going to work here, or at least I would not know how to make it work.

Any thoughts?
 
From my perspective, a little VBA trickery is in order. We could monitor those cells you want automatic line breaks. Can you ask the users to use some character to split the lines? Like a ^ or * or |? So when a user enters text like this:
The Grey fox jumped over the black cat|A marble can roll far on a hill|Opal is an old fashioned name

When the user pressed enter the macro would replace the | with a line break to make the text like this:
The Grey fox jumped over the black cat
A marble can roll far on a hill
Opal is an old fashioned name

Ideas?
 
Upvote 0
That could work. I would like to have it where they didn't have to do anything but they should probably just be happy that I'm making it all as easy for them as I already am.

I'm good with that. I have done some VBA in the past but that is not one I've tried, plus I'm self taught so it's always hit and miss.
 
Upvote 0
From my perspective, a little VBA trickery is in order. We could monitor those cells you want automatic line breaks. Can you ask the users to use some character to split the lines? Like a ^ or * or |? So when a user enters text like this:
The Grey fox jumped over the black cat|A marble can roll far on a hill|Opal is an old fashioned name

When the user pressed enter the macro would replace the | with a line break to make the text like this:
The Grey fox jumped over the black cat
A marble can roll far on a hill
Opal is an old fashioned name

Ideas?
Hey Jeffrey, Do you have a basic nudge for me on getting started with the script to do that? I actually don't know where to start with the language.
 
Upvote 0
I can write something generic. You'll just need to create a named range that encompasses the cells you want to monitor
 
Upvote 0
Give this a try. Add the code below to the SHEET module on the sheet that has the named range "MonitorCells"

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  
  Dim i As Range
  Dim Cel As Range
  Dim aStr As String
  
  Set i = Intersect(Target, Range("MonitorCells"))
  If Not i Is Nothing Then
    Application.EnableEvents = False
    For Each Cel In i
      aStr = Cel.Value
      If InStr(aStr, "|") > 0 Then
        aStr = Replace(aStr, "| ", vbCrLf)    'First remove spaces after |
        aStr = Replace(aStr, "|", vbCrLf)     'Then remove | without space
        Cel.Value = aStr
      End If
    Next Cel
  
    Application.EnableEvents = True
  End If

End Sub
 
Upvote 0
Solution
Give this a try. Add the code below to the SHEET module on the sheet that has the named range "MonitorCells"

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 
  Dim i As Range
  Dim Cel As Range
  Dim aStr As String
 
  Set i = Intersect(Target, Range("MonitorCells"))
  If Not i Is Nothing Then
    Application.EnableEvents = False
    For Each Cel In i
      aStr = Cel.Value
      If InStr(aStr, "|") > 0 Then
        aStr = Replace(aStr, "| ", vbCrLf)    'First remove spaces after |
        aStr = Replace(aStr, "|", vbCrLf)     'Then remove | without space
        Cel.Value = aStr
      End If
    Next Cel
 
    Application.EnableEvents = True
  End If

End Sub
That works perfectly. It would have taken me forever to figure something out like this.
Thank you so much!
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,917
Members
453,766
Latest member
Gskier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top