Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

autoexpand the Excel 2010 formula bar to display as much of the cell contents as possible

Latest post Fri, Apr 6 2012 1:30 PM by jomili. 8 replies.
  • Wed, Aug 31 2011 6:12 PM

    • PeterG
    • Top 10 Contributor
    • Joined on Mon, Feb 25 2008
    • Posts 194
    • Points 2,963

    autoexpand the Excel 2010 formula bar to display as much of the cell contents as possible

    I'm using the following code in a class module to autoexpand the Excel 2010 formula bar to show as much of the cell contents as possible.

    The business line where the formula bar height lH is calculated uses a parameter of 160 which seems to work ok for me with my screen size. This number is the number of characters that will fill up one line of the formula bar.

    I would welcome any suggestions on how this parameter might be calculated in the vba code so that the code would work effectively on other screen sizes, or for that matter, any other approaches to getting the formula bar to automatically expand to show the full cell contents.

    Public WithEvents xlApp As Application

    Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
        Dim lH As Long
            If Target.Cells.Count = 1 Then
                With Target
                    lH = Len(.Value2) - Len(Replace(.Value2, Chr(10), "")) + 1 + Len(.Value2) \ 160
                    On Error Resume Next
                    Do
                        Application.FormulaBarHeight = lH
                        If Err.Number > 0 Then
                            Err.Clear
                            lH = lH - 1
                        Else
                            Exit Do
                        End If
                    Loop
                End With
            End If
    End Sub

    Filed under:
    • Post Points: 21
  • Thu, Sep 1 2011 11:42 AM In reply to

    • Jim Cone
    • Top 10 Contributor
    • Joined on Tue, Jan 15 2008
    • Portland, Oregon - USA
    • Posts 243
    • Points 3,503

    Re: autoexpand the Excel 2010 formula bar to display as much of the cell contents as possible


    Some info that may/may not help...
    My monitor is a 27 inch with a screen resolution of 2560 x 1440.
    The number of characters displayed on one line in the formula bar, on 3 versions of Excel that I tested, ran from 241 to 256.

    You can get screen resolution using this code...
    '---
     Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
     
     Public Function GetSR() As Variant
      'x and y
       GetSR = Array(GetSystemMetrics(0), GetSystemMetrics(1))
     End Function

    Sub DoesSizeMatter()
     Dim vSize As Variant
     vSize = GetSR
     MsgBox "Screen Resolution is " & vSize(0) & " x " & vSize(1)
    End Sub
    '---
    You might be just a well off by setting the number of formula bar lines to two more than the number of carriage returns?
    '---
    Jim Cone
    Portland, Oregon USA
    http://www.mediafire.com/PrimitiveSoftware
    (XL Companion add-in:  compares, matches, counts, lists, finds, deletes...)

     

     

    • Post Points: 21
  • Fri, Sep 2 2011 10:15 AM In reply to

    • PeterG
    • Top 10 Contributor
    • Joined on Mon, Feb 25 2008
    • Posts 194
    • Points 2,963

    Re: autoexpand the Excel 2010 formula bar to display as much of the cell contents as possible

    Jim

    Thanks for the hint - I've not used it but for reference and anyone else to use, here is some better code for a class module to autofit the formula bar.

    Peter

    Option Explicit

    Public WithEvents xlApp As Application

    Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
        Dim lH As Long
            If Target.Cells.Count = 1 Then
                If IsError(Target) Then Exit Sub
                With Target
                    lH = CountL(Target)
                    On Error Resume Next
                    Do
                        Application.FormulaBarHeight = lH
                        If Err.Number > 0 Then
                            Err.Clear
                            lH = lH - 1
                        Else
                            Exit Do
                        End If
                    Loop
                End With
            End If
    End Sub

    Private Function CountL(ByRef rng)
        Dim s As Variant, lC As Long, lW As Long, sDisp As String
        lW = Int(198 * Application.Width / 1272)
        If rng.HasFormula Then
            sDisp = rng.Formula
        Else
            sDisp = rng.Value2
        End If
        s = Split(sDisp, vbLf)
        For lC = LBound(s) To UBound(s)
            CountL = CountL + 1 + Len(s(lC)) \ lW
        Next lC
        If CountL = 0 Then CountL = 1
    End Function

    • Post Points: 5
  • Sun, Sep 4 2011 6:27 AM In reply to

    • PeterG
    • Top 10 Contributor
    • Joined on Mon, Feb 25 2008
    • Posts 194
    • Points 2,963

    Re: autoexpand the Excel 2010 formula bar to display as much of the cell contents as possible

    Of course I should have used

    "If Target.Cells.CountLarge = 1 Then"

    at line 3 of the sheet selectionchange code

    • Post Points: 21
  • Thu, Mar 29 2012 12:33 PM In reply to

    • jomili
    • Top 500 Contributor
    • Joined on Thu, Mar 29 2012
    • Posts 3
    • Points 47

    Re: autoexpand the Excel 2010 formula bar to display as much of the cell contents as possible

    Peter,

    I've tried to use your code by pasting it into a Class Module in my Personal.xlsb, making the last change you mentioned ("Large").  However, when I click on a cell having multiple lines, my formula bar doesn't expand.  Can you advise on what I might be doing wrong? 

    • Post Points: 21
  • Fri, Mar 30 2012 2:07 AM In reply to

    • PeterG
    • Top 10 Contributor
    • Joined on Mon, Feb 25 2008
    • Posts 194
    • Points 2,963

    Re: autoexpand the Excel 2010 formula bar to display as much of the cell contents as possible

    With a class module you need to instantiate (i.e. start) the class before it becomes active.

    To do this place the following line of code in a the declaration area of an ordinary VBA module

    Public xlApplication As New clsFormulaBar

    and then have a subroutine or function to start the class running

    Function StartFormulaBarClass
       Set xlApplication.xlApp = Application
    End Function

    If you want to stop the class running then run a subroutine or function to do that

    Function StopFormulaBarClass
       Set xlApplication.xlApp = Nothing
    End Function

    I should say that the code is not perfect and you may want to add a maximum for the size that the formula bar expands by limiting the size of my variable lH

    If you want to find out more about class modules (or (m)any other aspects of Excel) then Chip Pearson's site is a fabulous resource

    http://www.cpearson.com/excel/Classes.aspx

    Peter

    • Post Points: 21
  • Thu, Apr 5 2012 11:06 AM In reply to

    • jomili
    • Top 500 Contributor
    • Joined on Thu, Mar 29 2012
    • Posts 3
    • Points 47

    Re: autoexpand the Excel 2010 formula bar to display as much of the cell contents as possible

    Peter,

    Thanks for all the help, but I'm still running into a problem.  I'm trying it out in a specific workbook rather than my personal, until I'm sure I can get it running okay. 

    Okay, here's what I've done (apologies in advance; I don't see how to wrap my code): 

    In a standard, ordinary module (Module1) I've pasted this:

    Public xlApplication As New clsFormulaBar

    Function StartFormulaBarClass()
       Set xlApplication.xlApp = Application
    End Function

    In my ThisWorkbook I've pasted this:

    Private Sub Workbook_Open()
    StartFormulaBarClass

    End Sub

    In a Class module I've pasted this:

    Option Explicit

    Public WithEvents xlApp As Application

    Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
        Dim lH As Long
            If Target.Cells.CountLarge = 1 Then
                If IsError(Target) Then Exit Sub
                With Target
                    lH = CountL(Target)
                    On Error Resume Next
                    Do
                        Application.FormulaBarHeight = lH
                        If Err.Number > 0 Then
                            Err.Clear
                            lH = lH - 1
                        Else
                            Exit Do
                        End If
                    Loop
                End With
            End If
    End Sub

    Private Function CountL(ByRef rng)
        Dim s As Variant, lC As Long, lW As Long, sDisp As String
        lW = Int(198 * Application.Width / 1272)
        If rng.HasFormula Then
            sDisp = rng.Formula
        Else
            sDisp = rng.Value2
        End If
        s = Split(sDisp, vbLf)
        For lC = LBound(s) To UBound(s)
            CountL = CountL + 1 + Len(s(lC)) \ lW
        Next lC
        If CountL = 0 Then CountL = 1
    End Function

    I'm getting a compile error on this:

    Public xlApplication As New clsFormulaBar

    The error is "User-defined type not defined"

    • Post Points: 21
  • Thu, Apr 5 2012 3:52 PM In reply to

    • PeterG
    • Top 10 Contributor
    • Joined on Mon, Feb 25 2008
    • Posts 194
    • Points 2,963

    Re: autoexpand the Excel 2010 formula bar to display as much of the cell contents as possible

    I omitted a key instruction. You must change the name of the class module to clsFormulaBar.

    Peter

    • Post Points: 21
  • Fri, Apr 6 2012 1:30 PM In reply to

    • jomili
    • Top 500 Contributor
    • Joined on Thu, Mar 29 2012
    • Posts 3
    • Points 47

    Re: autoexpand the Excel 2010 formula bar to display as much of the cell contents as possible

    Aha!  That would do it. 

    I've got the code working now, and it seems to be working pretty well.  The only missing piece is the limiter you mentioned earlier.  Below is the way I attempted to implement the limiter.  It seems to work, but I'm certainly open if you know of a better way. 

    Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
        Dim lH As Long
            If Target.Cells.CountLarge = 1 Then
                If IsError(Target) Then Exit Sub
                With Target
                    lH = CountL(Target)
                    If lH > 25 Then 'change to set maximum formula bar height
                    lH = 25
                    End If
                    On Error Resume Next
                    Do
                        Application.FormulaBarHeight = lH
                        If Err.Number > 0 Then
                            Err.Clear
                            lH = lH - 1
                        Else
                            Exit Do
                        End If
                    Loop
                End With
            End If
    End Sub

    • Post Points: 5
Page 1 of 1 (9 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.