Code needed that will divide a string of text between multiple cells if needed.

[dsheard2015]

Hello,

I have a worksheet with three columns; column A is for the date, column B is for the student initials, and column C is where the instructor will enter his comments based on the training that day. My question is regarding the comments added in column C. Just to give a brief layout of this form, columns A and B are relatively small and column C covers nearly 3/4 of the row on the page where the comments get entered into. These comments vary in length, they may be anywhere from just a few words such as "student did not fly due to aircraft maintenance", or comments that literally will fill up 10 rows or more for that same date.

Here is my problem...

When the instructors enter their comments they have to always pay attention to how much data they are entering in the cell and have to physically enter down to the next cell when they get to the end. A lot of times the comments go outside of the cell in which case they fall outside of the printable limits of that page.

I am hoping that VBA code can solve this problem for me. I would really like have the ability to enter all the comments for that day without needing to break concentration or having to think about entering down to the next cell and when the comments are completed, vba will be used to fill the first row and then fill the second row, and third..... until the entire comment has been entered.

Is this possible? I have entered the existing code that this sheet has at the end of this post. Any help with this is greatly appreciated.

Dave

Private Sub Worksheet_Activate()


Dim rLockable As Range
Dim cl As Range
Dim rng1 As Range 'date
Dim rng2 As Range 'student initials
Dim rng3 As Range 'instructor comments
Dim aer As AllowEditRange
Dim Sh As Worksheet


Set Sh = ThisWorkbook.ActiveSheet
Set rng1 = ActiveSheet.Range("A8:A53, A64:A109, A120:A165, A176:A221, A232:A277, A288:A333, A344:A389, A400:A445, A456:A501, A512:A557")
Set rng2 = ActiveSheet.Range("B8:B53, B64:B109, B120:B165, B176:B221, B232:B277, B288:B333, B344:B389, B400:B445, B456:B501, B512:B557")
Set rng3 = ActiveSheet.Range("C8:C53, C64:C109, C120:C165, C176:C221, C232:C277, C288:C333, C344:C389, C400:C445, C456:C501, C512:C557")


On Error Resume Next
On Error GoTo 0


ActiveSheet.Unprotect Password:=Sheets("Worksheet Names").Range("H27").Value


For Each cl In rng1
    If Sheets("MASTER INDEX").Range("AB1").Value = 1 Then
        cl.Locked = False
            ElseIf Sheets("MASTER INDEX").Range("AB1").Value = 2 Then
                cl.Locked = True
                    ElseIf Sheets("MASTER INDEX").Range("AB1").Value = 3 And cl.Value = "" Then
                        cl.Locked = False
                            ElseIf Sheets("MASTER INDEX").Range("AB1").Value = 4 Then
                                cl.Locked = True
                                    ElseIf Sheets("MASTER INDEX").Range("AB1").Value = 5 Then
                                        cl.Locked = True
                                            Else
                                                cl.Locked = True
    End If
Next cl
        
For Each cl In rng2
    If Sheets("MASTER INDEX").Range("AB1").Value = 1 Then
        cl.Locked = False
            ElseIf Sheets("MASTER INDEX").Range("AB1").Value = 2 Then
                cl.Locked = True
                    ElseIf Sheets("MASTER INDEX").Range("AB1").Value = 3 Then
                        cl.Locked = True
                            ElseIf Sheets("MASTER INDEX").Range("AB1").Value = 4 Then
                                cl.Locked = True
                                    ElseIf Sheets("MASTER INDEX").Range("AB1").Value = 5 And cl.Value = "" Then
                                        cl.Locked = False
                                            Else
                                                cl.Locked = True
    End If
Next cl


For Each cl In rng3
    If Sheets("MASTER INDEX").Range("AB1").Value = 1 Then
        cl.Locked = False
            ElseIf Sheets("MASTER INDEX").Range("AB1").Value = 2 Then
                cl.Locked = True
                    ElseIf Sheets("MASTER INDEX").Range("AB1").Value = 3 And cl.Value = "" Then
                        cl.Locked = False
                            ElseIf Sheets("MASTER INDEX").Range("AB1").Value = 4 Then
                                cl.Locked = True
                                    ElseIf Sheets("MASTER INDEX").Range("AB1").Value = 5 Then
                                        cl.Locked = True
                                            Else
                                                cl.Locked = True
    End If
Next cl


For Each aer In ActiveSheet.Protection.AllowEditRanges
aer.Delete
Next aer


    ActiveSheet.Protection.AllowEditRanges.Add Title:="Date", Range:=Range("A8:A53,A64:A109,A120:A165,A176:A221,A232:A277,A288:A333,A344:A389,A400:A445,A456:A501,A512:A557"), Password:=Sheets("OVERRIDE PASSWORD").Range("G14").Value
    ActiveSheet.Protection.AllowEditRanges.Add Title:="Instructor Comments", Range:=Range("C8:C53,C64:C109,C120:C165,C176:C221,C232:C277,C288:C333,C344:C389,C400:C445,C456:C501,C512:C557"), Password:=Sheets("OVERRIDE PASSWORD").Range("G14").Value
    ActiveSheet.Protection.AllowEditRanges.Add Title:="Review", Range:=Range("B8:B53,B64:B109,B120:B165,B176:B221,B232:B277,B288:B333,B344:B389,B400:B445,B456:B501,B512:B557"), Password:=Sheets("OVERRIDE PASSWORD").Range("G14").Value


ActiveSheet.Protect Password:=Sheets("Worksheet Names").Range("H27").Value


End Sub