Results 1 to 3 of 3

Thread: Count the number of lines in an active x textbox?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2014
    Posts
    2,312
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Count the number of lines in an active x textbox?

    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

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Count the number of lines in an active x textbox?

    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...

    Code:
    Ubound(Split(Worksheets("sheet1").TextBox1.Value, vbCr))+1
    Change the sheet name accordingly.
    Last edited by Domenic; Sep 20th, 2019 at 06:40 PM.

  3. #3
    Board Regular
    Join Date
    Aug 2013
    Posts
    67
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count the number of lines in an active x textbox?

    Hi Tony,

    This should suffice hopefully:

    Code:
    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •