Align TextBox Within A Range

[p45cal]

Also the .Height of a cell is not always the same as its .rowheight.
You might have to go about setting the textbox height to Range("A356:A375").Height and even then the textbox bottom isn't always in line with the bottom cell if you look at it at different zoom levels.

[Anshu]

Thanks for the reply!

Looks like row height can only be set in 0.25 increments:
vba set => actual height

It's good to see the technical aspect of the problem, but I'm unable to use this information to reslove my problems.
However, it inspires me to manually calculate the height of a single row after each new line. But I do not find any pattern which can help me to fix the issue. Here is the result...

Height.jpg

[Anshu]

Also the .Height of a cell is not always the same as its .rowheight.
You might have to go about setting the textbox height to Range("A356:A375").Height and even then the textbox bottom isn't always in line with the bottom cell if you look at it at different zoom levels.

As suggested, I've added the a new line "ActiveSheet.Shapes("TextBox 2").Height = Range("A356:A375").RowHeight * 20" in the macro to set the textbox height to Range("A356:A375").Height. And yes, you are right, even then the textbox bottom isn't always in line with the bottom cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Overall Observation
    If Target.Cells.Count = 1 And IsEmpty(Target) Or Not IsEmpty(Target) Then
       If ActiveSheet.Shapes("TextBox 2").Height < 171 Then
           Let ActiveSheet.Shapes("TextBox 2").Height = 171
           Range("A356:A375").RowHeight = ActiveSheet.Shapes("TextBox 2").Height / 20
           ActiveSheet.Shapes("TextBox 2").Height = Range("A356:A375").RowHeight * 20
         Else
           Range("A356:A375").RowHeight = ActiveSheet.Shapes("TextBox 2").Height / 20
           ActiveSheet.Shapes("TextBox 2").Height = Range("A356:A375").RowHeight * 20
       End If
    End If
End Sub

So, do I lose hope???

[p45cal]

As suggested, I've added the a new line "ActiveSheet.Shapes("TextBox 2").Height = Range("A356:A375").RowHeight * 20" in the macro to set the textbox height to Range("A356:A375").Height.

No you haven't. You're still using .Rowheight.

try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Overall Observation
If Target.Cells.Count = 1 And IsEmpty(Target) Or Not IsEmpty(Target) Then
  If ActiveSheet.Shapes("TextBox 2").Height < 171 Then Let ActiveSheet.Shapes("TextBox 2").Height = 171
  Range("A356:A375").RowHeight = ActiveSheet.Shapes("TextBox 2").Height / 20
  ActiveSheet.Shapes("TextBox 2").Height = Range("A356:A375").Height '<<<<<<<<<<<this line uses .Height.
End If
End Sub

I'm not sure what you're trying to achieve; if the textbox must always be big enough to fit some text or not. Give us a clue about the order you want things to happen in. Perhaps some narrative of the intention behind the code.

ps those two lines in your code:

Range("G379").Value = Range("AA368").Height
Range("J379").Value = Rows("368").EntireRow.Height

will always produce the same value; you might get more from:

Range("G379").Value = Range("AA368").Height
Range("J379").Value = Range("AA368").RowHeight

which will sometimes give different values.