Creating a loop

[HomeTek]

This is probably a simple thing for many of you, but I can't figure it out.

I have an Active X Text Box which has a default size set. if the user enters more than 9 lines of data then the text box starts to increase size every time a new line of data is added.

Now this code is going to get extremely long if I keep manually adding extra lines, but I'm presuming there is a way to convert the below code into loop format?

Thanks

Sub LineCount()
    Select Case Sheets("Test Sheet").tbDetails.LineCount
            Case 0, 1, 2, 3, 4, 5, 6, 7, 8, 9
                Sheets("Test Sheet").tbDetails.Height = 110
            Case 10
                Sheets("Test Sheet").tbDetails.Height = 120
            Case 11
                Sheets("Test Sheet").tbDetails.Height = 130
            Case 12
                Sheets("Test Sheet").tbDetails.Height = 140
            Case 13
                Sheets("Test Sheet").tbDetails.Height = 150
            Case 14
                Sheets("Test Sheet").tbDetails.Height = 160
                       
    End Select
    
    Sheets("Test Sheet").Range("A24").RowHeight = ActiveSheet.Shapes("tbDetails").Height + 10
End Sub

 

[Fluff]

How about

   Dim x As Long
   x = Sheets("Test Sheet").tbDetails.LineCount
   Sheets("Test Sheet").tbDetails.Height = Application.Max(110, 10 * x + 20)

 

[Yongle]

This appearsto be current logic

10 is 20 + 10 X 10 =120
11 is 20 + 11 X 10 = 130
etc

height = 20 + the number of rows X 20

So you could use this

    Select Case Sheets("Test Sheet").tbDetails.LineCount
            Case 0, 1, 2, 3, 4, 5, 6, 7, 8, 9
                Sheets("Test Sheet").tbDetails.Height = 110
            Case Is > 9
                Sheets("Test Sheet").tbDetails.Height = 20 + (Sheets("Test Sheet").tbDetails.LineCount * 10) 
    End Select

But @Fluff method is more concise

 

[Special-K99]

No VBA expert but isn't it simply this (without the need for adding more lines)

If (Sheets("Test Sheet").tbDetails.LineCount<=9) Then 110
Else
Sheets("Test Sheet").tbDetails.Height = 20 + Sheets("Test Sheet").tbDetails.LineCount * 10

The height is equal to 20 + 10 * the linecount without the need to add further If/Case statements

 

[Joe4]

In this case, CASE is not your best bet. This loop: .

    Select Case Sheets("Test Sheet").tbDetails.LineCount
            Case 0, 1, 2, 3, 4, 5, 6, 7, 8, 9
                Sheets("Test Sheet").tbDetails.Height = 110
            Case 10
                Sheets("Test Sheet").tbDetails.Height = 120
            Case 11
                Sheets("Test Sheet").tbDetails.Height = 130
            Case 12
                Sheets("Test Sheet").tbDetails.Height = 140
            Case 13
                Sheets("Test Sheet").tbDetails.Height = 150
            Case 14
                Sheets("Test Sheet").tbDetails.Height = 160
                       
    End Select

can be simplified to:

    Dim lcount as Long
    lcount = Sheets("Test Sheet").tbDetails.LineCount
    If lcount <= 9 Then
        Sheets("Test Sheet").tbDetails.Height = 110
    Else
        Sheets("Test Sheet").tbDetails.Height = lcount * 10 + 20
    End If

 

[HomeTek]

Many thanks all.

I tried the first couple of codes which worked a treat, but I will be experimenting with all of the code mentioned here so I can get to grips with how they work. I'd rather understand how the code works instead of just using it but not knowing why it works.

One last question though. It's not a massive deal, but after I have finished typing the text and click out of the text box, the first line of text always disappears. I don't mean actually disappears but it seems that the first line of text starts just outside the top of the text box if that makes any sense at all. So to the person looking at the text box the first line of text they will see is line 2.

It was doing this before I amended the code and still doing this now it has been changed.