HomeTek

New Member
Joined
Jan 27, 2017
Messages
42
Office Version
  1. 365
Platform
  1. Windows
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

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

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,012
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
   Dim x As Long
   x = Sheets("Test Sheet").tbDetails.LineCount
   Sheets("Test Sheet").tbDetails.Height = Application.Max(110, 10 * x + 20)
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
Code:
    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
 
Last edited:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
No VBA expert but isn't it simply this (without the need for adding more lines)

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,935
Office Version
  1. 365
Platform
  1. Windows
In this case, CASE is not your best bet. This loop: .
Code:
    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:
Code:
    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

New Member
Joined
Jan 27, 2017
Messages
42
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,712
Messages
5,597,720
Members
414,168
Latest member
Manapo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top