Formula box is covering cells I want to click

[Ineedsomehelp]

I am trying to sum some cells but when I add 2 cells the function box becomes to big (these cells are results or formulas of there own) and covers other cells that I want to click. Is there a way to restrict the function box to a certain size ( 1 line or 2 ) I still want the function bar to exist because I would like to track my formula.

If you know anything that could help I would appreciate it.

Thanks
Andrew

 

[Norie]

Andrew

Just move the box.

 

[Greg Truby]

I have a custom toolbar button to toggle the formula bar's visibilty on/off; so that's what I end up doing in this situation. If there's a way to restrict the max height of the formula bar, I've yet to learn of it...

 

[Greg Truby]

Just move the box.

You can do that??!!

 

[Norie]

Greg

I thought the OP was referring to the box that comes up when you type in say =SUMIF( which lists the various arguments.

You can move that but maybe I'm wrong about what the OP is referring to.

 

[Ineedsomehelp]

Hi there I am referring to the white box that shows the contents of your formula that you are creating. I am unable to move the box. So when my formula gets to large and covers a cell that I want to click I get stuck.
I know I can just type in the cell reference (C5) which is good because in the formula all you see is C5 but if you click C5 I get this in the formula box
GETPIVOTDATA("Person
Day",$A$3,"District
","BRT","CC
#","735","Proj
#","D004")
and after a couple of these my formual box is 10 lines deep and covering the first 5 rows.
I was looking for a way to restrict the formula box.

Thanks

 

[Ineedsomehelp]

There has to be a way to restrict the formula bar from expanding

 

[Ineedsomehelp]

what about when you click a cell just getting the cell reference (T5) rather than the underlining formula for that cell for the formula you are trying to formulate.

 

[Greg Truby]

AFAIK you'd have to use an API call, like one can use to alter the width of the named-range combobox control on the left of the formula bar. However API calls are not my strong suit. I'm assuming the formula editing area is an edit control. If that assumption is correct, then if I were to hazard a guess, I'd say that EM_SETRECT might be the parameter we'd need to set; but I fear I don't know how to find the handle we'd need in order to hit the correct control object.

lResult = SendMessage(      // returns LRESULT in lResult
     (HWND) hWndControl,      // handle to destination control
     (UINT) EM_SETRECT,      // message ID
     (WPARAM) wParam,      // = (WPARAM) () wParam;
    (LPARAM) lParam      // = (LPARAM) (LPRECT) lParam; );

 

[Tom Urtis]

Hide a formula bar when a formula or text is above a certain ceiling

The problem is, more than just formulas, if you have a lot of text in the cell, that that can also block out the entire sheet. Even if you could dock the formula bar elsewhere, it would still protrude onto your sheet's work area so that would not give you much of an advantage.

You probably know you can go to Tools > Options > View tab, and deselect Formula bar in the Show section. If you record a macro to do it, you'd see this code line:
Application.DisplayFormulaBar = False

To automatically hide the formula bar when either a cell's formula length or text length is greater than, say, 50, try this:

Right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet. Modify for character ceiling if 50 is too many or not enough.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
If Len(Target.Text) > 50 Or Len(Target.Formula) > 50 Then
Application.DisplayFormulaBar = False
Else
Application.DisplayFormulaBar = True
End If
End Sub