Count the number of lines in an active x textbox?

[tonywatsonhelp]

Hi everyone,

Is there a vba that can count the number of rows in an active x text box (I have multi rows selected, I want to know how many there are?)


basically if I was writing it in English what I want is

Textbox1 number of lines = ???

please help if you can

thank

Tony

 

[Domenic]

The new line character for a textbox is denoted by the constant vbCr or its equivalent value Chr(13). Therefore, we can use the Split function to return an array of lines based on vbCr as the delimiter. Then we can use Ubound to return the number of elements in the array, which represents the number of lines. However, since Split() returns a zero-based array, we'll need to add 1 to the result returned by Ubound. So, assuming that Sheet1 contains your ActiveX textbox, we would have the following...

Ubound(Split(Worksheets("sheet1").TextBox1.Value, vbCr))+1

Change the sheet name accordingly.

 

[pella88]

Hi Tony,

This should suffice hopefully:

Sub CountLines()

Dim wb As Workbook
Dim ws As Excel.Worksheet


Set wb = ThisWorkbook
Set ws = wb.Sheets(1)


LineCount = UBound(Split(ws.Shapes("TextBox 1").TextFrame2.TextRange.Characters.Text, Chr(10))) + 1


MsgBox LineCount


End Sub

Please replace TextBox 1 with the name of your TextBox.

Br
pella88