In Excel 2010, how can I make a row on a non-active sheet autofit when a cell in that row changes it's value due to a formula referencing the active sheet?

Posted on 2014-08-10
Last Modified: 2014-09-01
I have a formula in a cell on a non-active sheet.  It has a formula in it that gets it's data from the active sheet.  ie: ='Mysheet1'!A1
When I set up each sheet, I formatted the rows autofit.  When I enter text data in the referenced cell in the active sheet and that data is too long to fit in the cell, it makes the row taller so that all the text can be seen in the cell.  But when I go and look at the non-active sheet, I see that the row height has not changed to accommodate the text.

How can I make it do this?

Right now, I've got a work-around that is not optimal.  When I activate the sheet with the cell that contains the formula, I capture that sheet's Activate event and autofit the rows, but this has a flashing effect.  Even when I disable events, calcs and screen updating.  If it didn't clash, this would probably be ok.

How can I do this and not get that flashing effect?
Question by:cashonly
    1 Comment
    LVL 20

    Accepted Solution

    A formula does not trigger autofit.
    Use the worksheet calculate event on the formula sheet to do it.
    The sheet does not have to be active, so no screen flashing.

    Private Sub Worksheet_Calculate()
    End Sub

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Suggested Solutions

    DECT technology has become a popular standard for wireless voice communication. DECT devices are not likely to be affected by other electronic devices and signals because they operate in a separate frequency-band.
    This paper addresses the security of Sennheiser DECT Contact Center and Office (CC&O) headsets. It describes the DECT security chain comprised of “Pairing”, “Per Call Authentication” and “Encryption”, which are all part of the standard DECT protocol.
    Viewers will learn how to customize the ribbon and quick access toolbar in Excel 2013.
    Viewers will learn how to share Excel data with others from desktop Excel, as well as Excel Online via OneDrive, and embed an Excel file on a website.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now