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!:confused:

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

3431

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

[Anshu]

Thank you for the Active X textBox! It works, but only for height. The width of the textbox is shrinked automatically. This is a main reason I prefer to use normal TextBox, because width remain static for normal textbox.

[p45cal]

Try something along these lines:
Private Sub TextBox1_GotFocus()
With TextBox1
.AutoSize = True
.Left = Range("B1").Left
.Top = Range("A356").Top
.Width = 590
.EnterKeyBehavior = True
End With
End Sub

Private Sub TextBox1_LostFocus()
With TextBox1
.EnterKeyBehavior = False
.Top = Range("A356").Top
.Left = Range("B1").Left
.Width = 590
.AutoSize = False
ht = .Height
Debug.Print ht
.Height = Application.Max(171, ht)
Range("A356:A375").RowHeight = (.Height / 20) ' + 0.5
.Height = Range("A356:A375").Height
Do While .Height < ht
Range("A356:A375").RowHeight = Range("A356").RowHeight + 0.25
.Height = Range("A356:A375").Height
Loop
End With
End Sub

[Anshu]

Thanks a lot for helping me! It is working really well.

When I close the document after entering something in the active x textbox, a popup comes with the message...
"parts of your document may include personal information that can't be removed by document inspector"

It can be disabled in Trust Center > Trust Center Settings > Privacy Options, but my question is - if I share this file to someone, is this pop-up comes every time they make any change to the textbox?? This may be annoying for them.

[p45cal]

The file will retain the setting you give it in Trust Centre (as long as you save it), so that message should no longer pop up.

[Anshu]

Okay...thank you so much!!