Align TextBox Within A Range

[Anshu]

Hi!
My sheet have a text box named "TextBox 2" which is placed in the Range("B356:Z375"). The sheet contains a macro to auto fit the textbox within the same Range("A356:A375") irrespective of the height of the textbox, means, if the textbox is resized due to entry of texts, the rows adjust itself to fit with the height of the textbox. The minimum height of the textbox is fixed as 171.
(Please see the attachment below)

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
         Else
           Range("A356:A375").RowHeight = ActiveSheet.Shapes("TextBox 2").Height / 20
       End If
    End If
    
End Sub

Problem:
1. The macro above is running on selection of any cell on the sheet, which, I think, will create unnecessary burden on the system. It should run only after selection of the textbox.

2. Rows are not adjusted exactly as the height of textbox, i.e., text box height is moving a little bit inside or outside of the Range("A356:A375") in bottom, when the height is changed after entering text in the textbox.


Solution of Problem No. 2
One solution, I think, is to readjust the height of the textbox to align it with the Range("A356:A375") or between the row (356:375).

With Thanks!
Anshu

P.S.
I've noticed a strange thing. The UNDO and REDO option is not working due to this Macro.I don't understand what the hell is going on!

[p45cal]

Looks like row height can only be set in 0.25 increments:
vba set => actual height
7 => 7
7.1 => 7.25
7.2 => 7.25
7.3 => 7.25
7.4 => 7.25
7.5 => 7.5
7.6 => 7.75
7.7 => 7.75
7.8 => 8
7.9 => 8
8 => 8
8.1 => 8
8.2 => 8.25
8.3 => 8.25
8.4 => 8.5
8.5 => 8.5
8.6 => 8.75
8.7 => 8.75
8.8 => 8.75
8.9 => 8.75
9 => 9
9.1 => 9.25
9.2 => 9.25
9.3 => 9.5
9.4 => 9.5
9.5 => 9.5
9.6 => 9.5
9.7 => 9.75
9.8 => 9.75
9.9 => 10
10 => 10

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

[Anshu]

Thank you for the correction in the macro! Changing from .RowHeight to .Height solved my Problem number (2)

2. Rows are not adjusted exactly as the height of textbox

But Problem No 1 is still there, i.e, every time a cell is selected, the macro is running, impacting my pc performance. Also, this macro disables my UNDO option everytime I select any cell.

---

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.

Sorry if I have not explained properly. Let me explain again...

I want a textbox inside excel sheet with following conditions...
(1) TextBox must be always lies between the rows(356:375). (Solved above, works on 100% zoom level)
(2) TextBox width should be fixed (static) (No issue)
(3) TextBox height should be dynamic, that is, the height of textbox should increase or decrase according to the enteries so that all the text must be visible in the textbox. (The bottom text line is not visible in the textbox after applying the above macro)
(5) The target should be the textbox or range B356:Z375 (Whichever be possible) so that the code does not run everytime a cell is selected.

(Need not to mention, code should not impact the basic features like undo and redo)
Even I'm ready to use merged cell range, too, if they satisfy these needs. But I think that would be more difficult for VBA.

All these requirements may be satisfied easily if I use a single row, as I think. But the problem with a single row is -
If the text entries are very large, say in thousands words, the row height will be extremely large. Excel always show a row either from top or bottom, and hence, it'll be very difficult to read the 'Stories'.

Thank you for listening these too much stuff with too patience!

[p45cal]

Macros nearly always clear the undo stack - there's no avoiding it.
re "the textbox is resized due to entry of texts"; Where and how does the text box get its text from? Maybe we can get it to respond to that. Is it manually typed in by the user? Does a macro put text into it?
[There will be many solutions to this problem, I'm just trying to find a good one.]

[Anshu]

Where and how does the text box get its text from?

It is manually typed in by the user.

Does a macro put text into it?

No, not at all. Neither any linked cell formula, nor any macro. Just typing manually.

Macros nearly always clear the undo stack - there's no avoiding it.

This is really a big issue, then. May be, using change event instead of selection event may help! Can we declare an object like TextBox as Target??

[p45cal]

Check if the attached behaves in an acceptable way (it may not).