Excel row height annoyance

Like plenty of other people I use Excel to build all sorts of text based things.

I used Excel to build the 20 or 30 page html help file that comes with XLAnalyst (new web site and version due out possibly before Christmas, with lots of juicy xll based speedups).

I’ve also used it for all manner of dodgy data cleaning projects that should theoretically have been done in a database.


One thing that has regularly annoyed me is the bug some patronising dev introduced, possibly in XP. Where if you enter more than 1 line of text (eg with Alt+enter) when you leave the cell they helpfully extend the row height to ruin your spreadsheet. Doing cntrl z should put it right, that often tells Word to do as its told, not what it thinks you want, but no, in Excel you can’t turn it off. Or teach it to do what its been asked.

It dawned on me the other day that if I grab the row height on selection move and check it after data has been entered I can fix this usability blunder (perhaps the person responsible went on the found the ribbon team?).

So here it is:

Private dRowHeight As Double

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   On Error Resume Next
   dRowHeight = Target.RowHeight
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
   If dRowHeight > 0 Then Target.RowHeight = dRowHeight
   Debug.Print dRowHeight
End Sub

Now with this needing to be a global setting really, then it needs to go in a class. Then you have a VBA project floating around messing up the project explorer. So then it makes sense to use something else. Xlls don’t see the selection change event (sadly – they do see the data entered one). So that leaves a COM Add-in.

I already have a VB6 COM add-in to stop Excel launching its pointless reviewing toolbar every time I open a workbook I did not write. So maybe what I (/we?) need is a MyExcel add-in. What other Excel annoyances would you like to go away?

(All credit to Excel for making it possible to fix these issues, some products (and UI’s ;-)) aren’t so powerful.)

cheers

Simon

[ps does it still do this in 2007?]

Advertisements

8 Responses to “Excel row height annoyance”

  1. Jayson Says:

    Yep. Still happens in 2007.

  2. jonpeltier Says:

    “I already have a VB6 COM add-in to stop Excel launching its pointless reviewing toolbar every time I open a workbook I did not write.”

    So that’s why the stupid reviewing toolbar always comes up. What a PITA that is.

    The row height thing got me the other day. It gave me a four-inch-tall cell. I formatted the cell not to word wrap. Then I edited the cell’s text, the word wrap setting changed and the cell was 4″ tall again.

  3. Simon Says:

    Good point Jon I forgot about that, that really annoys me, set the row height to 12.75, set it to not word wrap, enter long string – bang – huuuge cell with words all nicely wrapped exactly how you told it not to.

    The reviewing thing was something ‘helpful’ one version of outlook did, it added a doc property (possibly called ‘annoy recipient’?). I kind of thought it was 2000 or 2002, and changed in 2003, but I could be wrong. I’ll post the code/tool once I have found it (and added any additional suggestions)

  4. JP Says:

    That Reviewing toolbar has to be the most annoying feature in Excel. Where does one acquire this VB6 add-in?

  5. Peter Grebenik Says:

    If you use my addin (http://excelusergroup.org/media/p/2362.aspx) for adding text then the row height is left unaltered even with if the cell contents contains line feeds.

  6. Controlling the review toolbar « Smurf on Spreadsheets Says:

    […] Controlling the review toolbar A while ago I mentioned a VB6 COM add-in I had for keeping that pesky review toolbar in its place. I also discussed controlling the wordwrap/ row height auto scroll oddness that Excel does when you enter a long text string here. […]

  7. Victor Says:

    I’m not too used to excel but the cure I found that works very well for me is this:

    1. Mark rows 1 to 999 using shift + mouse clicking (click on the row-number to the left).
    2. Right-click and set them to a specific height (even if it is already set). For me “15” is default (europe?).

    Then click any of the cells in rows 1 to 999 and enter multi-lined text using Alt + Enter.
    Voilà! The row height is no longer automatically adjusted by excel.
    It seems like excel has understood that you want a fixed row-height.

    I don’t know how well this works for others but for me it is a heavily used trick :)

  8. Bill Says:

    our excel 2003 has all of a sudden taken cells with data entered and shrinks them down to zero row height. Unhiding or doing a mass row height change have no effect. It doesn’t happen with every worksheet? It’s really easy for someone who’s not aware to miss those rows!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: