Ask Your Question

Macro to check if formula bar is visible

asked 2020-11-18 13:24:43 +0200

jmid35 gravatar image


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 flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2020-11-18 20:31:22 +0200

jmid35 gravatar image

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
after = cCtrl.VisibleArea.Height
wasVisible = (after<>before)
If wasVisible Then setVisibleFormulaBar(True)
isFormulaBarVisible = wasVisible

End Function

Thank You for your suggestion!

edit flag offensive delete link more


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

Lupp gravatar imageLupp ( 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.

JohnSUN gravatar imageJohnSUN ( 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.

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

answered 2020-11-18 18:58:24 +0200

Lupp gravatar image

updated 2020-11-18 19:00:34 +0200

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
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
args(0).Name = "InputLineVisible"
args(0).Value = pShow
frame = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("")
dispatcher.executeDispatch(frame, ".uno:InputLineVisible", "", 0, args())
End Sub
edit flag offensive delete link 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.

jmid35 gravatar imagejmid35 ( 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..

Lupp gravatar imageLupp ( 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.

jmid35 gravatar imagejmid35 ( 2020-11-18 21:47:51 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-11-18 13:24:43 +0200

Seen: 40 times

Last updated: Nov 18 '20