Enter multiple lines of text and auto-fit row height

[judgejustin]

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?

 

[Jeffrey Mahoney]

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?

 

[judgejustin]

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.

 

[judgejustin]

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.

 

[Jeffrey Mahoney]

I can write something generic. You'll just need to create a named range that encompasses the cells you want to monitor

 

[Jeffrey Mahoney]

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

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

 

[judgejustin]

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

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!