mistersend
New Member
- Joined
- May 29, 2020
- Messages
- 15
- Office Version
- 2019
- Platform
- Windows
I've inserted a TextBox ("TextBox 1") by Menu > Insert > Text > TextBox. I've inserted a Macro on the same sheet where the TextBox is present. Here is the Macro...
Description: I've added the Remarks with every line in the macro above to make my points clear. Let me explain here, too.
Solution I Need: The macro should run only when...
Thanks in advance!
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'If any cell is selected
If Target.Cells.Count = 1 And IsEmpty(Target) Or Not IsEmpty(Target) Then
' TextBox minimum height will not go below 171
If ActiveSheet.Shapes("TextBox 2").Height < 171 Then Let ActiveSheet.Shapes("TextBox 2").Height = 171
' Range("A356:A375").RowHeight will change if the height of the TextBox is changed so that the TextBox will remain always between the range ("A356:A375")
Range("A356:A375").RowHeight = (ActiveSheet.Shapes("TextBox 2").Height + 10) / 20
' TextBox height is adjusted to fit exactly with the Range("A356:A375") especially in bottom
ActiveSheet.Shapes("TextBox 2").Height = Range("A356:A375").Height
End If
End Sub
Description: I've added the Remarks with every line in the macro above to make my points clear. Let me explain here, too.
- The TextBox minimum height is fixed on 171 (Width of the TextBox is always static)
- The Text in the TextBox will be entered manually, not by any formula or macro.
- On entering any text, the TextBox is Auto Resized so that all the entered text will be clearly visible.
- As soon as any cell is selected, the Range("A356:A375") change its row height according to the height of the TextBox
- Then, the TextBox height is again adjusted to fit exactly with the Range("A356:A375").
Solution I Need: The macro should run only when...
- The TextBox is selected. AND
- Any text is changed inside the textbox manually
Thanks in advance!