kashif.special2005
Active Member
- Joined
- Oct 26, 2009
- Messages
- 443
Hi,
I have very unique problem here and from past 2 days I searched the answer in google or asked with some other VAB expert but I could not get a answer, please kindly help me to resolve this problem.
Problem:-
I have multiple footnotes (footnote cells C21, C44, C120, C178 etc.,) in a excel template, and one thing is common is that all footnotes comments are 5 row and 7 column merge with wraptext.
footnotes C21 range will be :- C21:I25, merged with wraptext
footnotes C44 range will be :- C44:I48, merge with wraptext
etc.,
User is entering the text in the cell with alt + Enter or just typing the text (I don't know user can enter the text as per their requirements), if user just typing the text it will automatically get in new line if the text is reached max length in the merge cells.
Now my task is that if the entered text in footnotes cells (C21, C44, C120, C178 etc.,) is clearly visible within the merged 5 rows then do nothing.
But if the entered text in the footnotes cells (C21, C44, C120, C178 etc., ) is not clearly visible then insert the required rows so that entered footnotes clearly visible to the respective managers when they received the user's filled templates.
I have no idea how can I achieve this, however I written down some code below which is working ok if the user entered large footnotes which is not clearly visible within the 5 rows, but I am stuck where the footnotes is clearly visible the below code then it should not create a row, but it is creating.
Is there any way we can read the lines, that mean how may lines in a merge wrapt text (including alt + enter and just normal text)
Kindly request to all VBA expert, please please help me to correct my code.
Thanks in advance
Kashif
I have very unique problem here and from past 2 days I searched the answer in google or asked with some other VAB expert but I could not get a answer, please kindly help me to resolve this problem.
Problem:-
I have multiple footnotes (footnote cells C21, C44, C120, C178 etc.,) in a excel template, and one thing is common is that all footnotes comments are 5 row and 7 column merge with wraptext.
footnotes C21 range will be :- C21:I25, merged with wraptext
footnotes C44 range will be :- C44:I48, merge with wraptext
etc.,
User is entering the text in the cell with alt + Enter or just typing the text (I don't know user can enter the text as per their requirements), if user just typing the text it will automatically get in new line if the text is reached max length in the merge cells.
Now my task is that if the entered text in footnotes cells (C21, C44, C120, C178 etc.,) is clearly visible within the merged 5 rows then do nothing.
But if the entered text in the footnotes cells (C21, C44, C120, C178 etc., ) is not clearly visible then insert the required rows so that entered footnotes clearly visible to the respective managers when they received the user's filled templates.
I have no idea how can I achieve this, however I written down some code below which is working ok if the user entered large footnotes which is not clearly visible within the 5 rows, but I am stuck where the footnotes is clearly visible the below code then it should not create a row, but it is creating.
Is there any way we can read the lines, that mean how may lines in a merge wrapt text (including alt + enter and just normal text)
Code:
Private Sub Expand_Footnotes_Rows(template)
Dim footnotes As Variant, vTemp As Variant
Dim iLoop As Integer, iLoop2 As Integer, iCount As Integer
Dim ws As Worksheet
Set ws = template.Worksheets("Call Template")
footnotes = Array(21, 44, 83, 144, 161, 181, 198, 362, 393, 463, 511)
For iLoop = LBound(footnotes) To UBound(footnotes)
ws.Cells(footnotes(iLoop), "Z").Value = "Footnotes"
Next iLoop
For iLoop = 21 To 1000
If ws.Cells(iLoop, "Z").Value = "Footnotes" Then
iCount = 0
vTemp = Split(ws.Cells(iLoop, "C").Value, Chr(10))
For iLoop2 = LBound(vTemp) To UBound(vTemp)
iCount = iCount + Int(Len(vTemp(iLoop2)) / 100)
Next iLoop2
If iCount > 0 Then
ws.Range(ws.Cells(iLoop + 4, "A"), _
ws.Cells(iLoop + 4 + (UBound(vTemp) + iCount - 4), "A")).EntireRow.Insert
ElseIf UBound(vTemp) > 0 Then
ws.Range(ws.Cells(iLoop + 4, "A"), _
ws.Cells(iLoop + 4 + UBound(vTemp) - 4, "A")).EntireRow.Insert
End If
End If
Next iLoop
ws.Range(ws.Cells(1, "Z"), ws.Cells(1000, "Z")).ClearContents
End Sub
Kindly request to all VBA expert, please please help me to correct my code.
Thanks in advance
Kashif