# Macro to check if formula bar is visible

Hello,

I have a Calc document and I would like to check if the formula bar is visible when the document opens. If it is visible, I would like to hide it and then make it visible again when the document closes.

I have figured out how to show/hide the formula bar using the macros shown in Link but I can not figure out to check if the formula bar was visible when the document opens.

Does anyone have any suggestions?

I am using Libreoffice 6.2 x64 on Windows 10

Thank You!

edit retag close merge delete

Sort by » oldest newest most voted

I think I found a workable solution. I modified your code @Lupp to use the VisibleArea.Height

Function isFormulaBarVisible() As Boolean

doc = ThisComponent
cCtrl = doc.CurrentController
before = cCtrl.VisibleArea.Height
setVisibleFormulaBar(False)
after = cCtrl.VisibleArea.Height
wasVisible = (after<>before)
If wasVisible Then setVisibleFormulaBar(True)
isFormulaBarVisible = wasVisible

End Function


more

Much less dirty, though not exactly satisfying.
Did you also consider an answer to my question "What good for?"?

( 2020-11-18 21:10:59 +0200 )edit

@jmid35 Following @loop, I also want to ask - why? Why do you need to know what state the Formula Bar is in? To implement your "to hide it and then make it visible again when the document closes" scenario, simply follow the procedure you found from the link twice - HideFormulaBar() after opening the document and HideFormulaBar(TRUE) before closing it.

( 2020-11-20 08:39:41 +0200 )edit

@JohnSUN The reason is, if someone opens the document and they do not have the formula bar visible in their profile then I do not want to make it visible when the document closes.

( 2020-11-20 12:49:45 +0200 )edit

If you can assure that the current view of the spreadsheet document actually shows cells, the viewed range will change if the FormulaBar was visible and got hidden. This very dirty idea leads to:

Function isFormulaBarVisible() As Boolean REM Occasional false positives expected!
REM This is a VERY dirty, but not exactly quick as-if-solution.
REM It is definitely assured to be NOT reliable: If the current view doesn't
REM actually show any cells, this may not change if the FotrmulaBar gets hidden.
doc = ThisComponent
cCtrl = doc.CurrentController
before = cCtrl.ReferredCells.AbsoluteName
setVisibleFormulaBar(False)
after = cCtrl.ReferredCells.AbsoluteName
wasVisible = (after<>before)
If wasVisible Then setVisibleFormulaBar(True)
isFormulaBarVisible = wasVisible
End Function


where setVisibleFormulaBar() is the Sub you already pointed to, just a bit adapted to the need (and to my personal naming preferences):

Sub setVisibleFormulaBar(Optional pShow As Boolean)
If IsMissing(pShow) Then pShow = True
Dim frame As Object
Dim dispatcher As Object
Dim args(0) As new com.sun.star.beans.PropertyValue
args(0).Name = "InputLineVisible"
args(0).Value = pShow
frame = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(frame, ".uno:InputLineVisible", "", 0, args())
End Sub

more

I tried that and it works when cell row height is less the the window space of the formula bar. However, if the cell row height is bigger it will fail.

( 2020-11-18 19:13:04 +0200 )edit

The code is dirty, and I neither know a clean way nor want to spend much time for the topic. A spreadsheet without the FormulaBar is simply a monster in my view. So are spreadsheet views not showing more than one row. What good for?

(Additional explanation: The controller property .ReferredCells is dirty itself: Even if not a single cell actually is visible, it describes a cell range of at lest one row and one column. The API generally doesn't know empty CellRange objects (or addresses for them) (nor an empty Text object e.g. - only empty strings as a surrogate, but with lots of properties). The backround surely are design decisions made decades ago..

( 2020-11-18 19:46:13 +0200 )edit

This spreadsheet is being used as an interface in a complex application and all the toolbars and the formula bar are hidden.

( 2020-11-18 21:47:51 +0200 )edit