Macro to Expand/Collapse Formula Bar

[RogerC]

I'm using a spreadsheet that serves the dual purpose of calculating project data and also presenting it to the project team. Understanding that Excel is not a presentation tool, I'm trying to make the presentation aspect as clear and quick as possible.

There are some cells I would always like to display in the expanded state of the Formula Bar and others I would never like to see expanded, which means I need to switch frequently between expanded/collapsed states.

If the cursor is entered into any cell in columns E or F, I'd like the Formula Bar to automatically expand. Conversely, if the cursor enters any cell in columns B thru D, or G thru P, I'd like the formula Bar to collapse.

Can this be done with a macro?

 

[VoG]

Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 5 Or Target.Column = 6 Then
    Application.FormulaBarHeight = 5
Else
    Application.FormulaBarHeight = 1
End If
End Sub

 

[Rick Rothstein]

Give this event code a try...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not Intersect(Target, Range("E:F")) Is Nothing Then
    Application.DisplayFormulaBar = True
  ElseIf Not Intersect(Target, Range("B:D,G:P")) Is Nothing Then
    Application.DisplayFormulaBar = False
  Else
    ' You did not say what should happen for all the other columns on a worksheet
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[RogerC]

Thank you VoG and Rick. Both of those work perfectly.