Automatically vertically resizing the Formula Box

[ellison]

Hi, I'm wondering if there's a setting or tweak that we can access which would "automatically" vertically resize the formula box

(I'm hoping that's the right name for it - the bit that's next to the Name Box as in the screenshot).

The issue is that we have some cells which are a) long and b) contain line breaks etc NB we are definitely in the process of tidying these up but it's long slog!

When people are skimming through the information, they're missing bits as the info is "tucked away in the bottom parts of the" cell...

Options which we've tried to give them but still aren't working:

1. Please get in the habit of hitting F2 to get the box to resize
To be fair to them, they're juggling a lot. And asking them to check each cell to see whether there is info tucked away into the bottom of the cell just won't work for them

2. Put the formatting on wrap text for that column / increasing column width / manually increasing the size of the box vertically
The issue is that they like to visually see the info from the rows above / below
And anything that restricts their view of the lines above / below (unless it's absolutely necessary!) gets them moaning and groaning

What I've been asked to ask is whether there may be a setting tucked away somewhere (maybe in the appearance settings?) which somehow automatically resizes the formula box to display all of the information contained in it.

That way acheive a better balance between them skimming through the info at speed AND seeing all of the relevant information

Thanks for taking a look, appreciated

 

[Fluff]

As far as I know, there is no way to auto resize the formula bar.

 

[ellison]

Thanks for taking a look Mr Fluff, much appreciated all the same!

 

[Fluff]

You're welcome.

 

[Georgiboy]

If you seperate your text with Alt+Return then the below will auto size the formula bar depending on how many line breaks there are in the text:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim L As Long, str As String
    
    If Target.Cells.Count > 1 Then Exit Sub
    str = Target.Value
    L = Len(str) - Len(Replace(str, Chr(10), ""))
    Application.FormulaBarHeight = L + 1
End Sub

 

[Georgiboy]

Onother option could be to end the text in the cell with something like **END**, that way if the user has not seen **END** they know they have not read all of the text.

You can also use CTRL+SHIFT+U to resize the formula bar to the last used manually set size.

 

[ellison]

Good advice form everybody, that's very kind thank you.

 

[Georgiboy]

You are welcome, thanks for the feedback.