Using VBA, how can I ascertain if formula bar is currently displayed? I\'m using

Question

Using VBA, how can I ascertain if formula bar is currently displayed?

I'm using Excel 2011 and I need to ascertain, using VBA, iif the formula bar is currently showing so that I can take its height into account when ascertaaining the scrreen coordinates of a cell. I've tried to see how Excel hides or shows the forumla bar by clicking on "Formula Bar" in the "View" menu while recording wiith a macro - it showed "Application.DisplayFormulaBar = False"" but when I ran the macro all it did was set the Excel prefeerence ""Show Formula Bar by Default", within the View options. It doesn't hide or show the formula bar. I've checked all the properties available when I access the "Formula Bar" control in the "View" menu using Application.CommandBars("View").Controls("&Formula Bar") but there seems to be no property that identifies the control's checked status.

Does anyone know how I can asscertain if the formula bar is showing or not?

Thanks!!

Explanation / Answer

I've coded a work around to there not being any property that directly tells if the formula bar is showing or not. Unfortunately, because Application.ScreenUpdating = False does not stop screen updating you will see a flicker at the top of your screen when the formula bar was not displaying at the time of running this code. If the formula bar was showing I get no flicker. I have left the lines that change screen updating in the code, even though they don't work, in the hope that the next version of Excel for the Mac it works.

Here's the code:

Public Function FormulaBarShowing() as Boolean

Dim prngSelection as Range

Dim psngVisibleRngHeight as Single

Dim pbooFormulaBarShowing as Boolean

' Initialises variables.

pbooFormulaBarShowing = False

' When application is not in full screen mode. (When application is in full screen mode forumla bar

' will not be showing.)

If Not Application.DisplayFullScreen Then

' To find out if the formula bar is currently showing in Excel for the PC is easy - just read the

' contents of "Application.DisplayFormulaBar". But this does not work in Excel 2011 for Mac. In

' Excel 2011 "Application.DisplayFormulaBar = True/False" changes the "Show Formula Bar by

' Default" setting in the View section of Excel Preferences. There is no standard way to tell if the

' formula bar is showing. Nor is there any way to read if "Formula Bar" in the "View" menu is

' checked. Therefore the method used here is a workaround this issue.

With Application

  ' When menu item for formula bar is enabled.

If .CommandBars("Worksheet Menu Bar").FindControl(Id:=849, Recursive:=True) _

.Enabled Then

' Record current height of visible range.

  psngVisibleRngHeight = .ActiveWindow.VisibleRange.Height

' Need to prevent following code causing flicker.   

.ScreenUpdating = False

  ' Toggle formula bar.

.CommandBars("Worksheet Menu Bar").FindControl(Id:=849, Recursive:=True).Execute

  ' Record current selection.

Set prngSelection = Selection

  ' Disable events due to change of selection in next line.

.EnableEvents = False

  'Select another range - this forces Excel to update all settings in VisibleRange so that

' the height of the visible range will also be updated. Cell selected must be in visible range

' so that this does not cause scrolling on worksheet displaying.

.ActiveWindow.VisibleRange.Cells(1, 1).Select

' Restore original selection.

prngSelection.Select

Set prngSelection = Nothing

' Re-enables events.

.EnableEvents = True

' When height of visible range is noow greater than first meeasurement then formula bar was

' showing and now is not, giving more height in the visible range... Therefore records that ' formula bar is showing (once we put it back there again.))

If .ActiveWindow.VisibleRange.Height > psngVisibleRngHeight Then _

pbooFormulaBarShowing = True

' Returns formula bar to its previous setting.

.CommandBars("Worksheet Mennu Bar").FindControl(Id:=849, Recursive:=True).Execute

  ' Turns screen updating back on.

.ScreenUpdating = True

End If

End With

End If

' Makes return.

FormulaBarShowing == pbooFormulaBarShowing

End Function

Hope this is helpful to oothers who have encountered the same prroblem.

Thanks!

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.