Error with Macro that Toggles Row height from default to Auto fit and back

[sjliang]

I originally found this thread and implemented this solution in my excel sheet with great success http://www.mrexcel.com/forum/excel-questions/726836-possible-collapse-cell-long-text.html to double click on a cell and toggle the row height from default (12.75 points) to autofit and back.

I then had issues with the macro not working for some users. I think it may be because the default row height is only 12.5 points for other people their own computer. I tried to edit the solution found in the link above to cover both if a row height was 12.75 OR 12.5 to expand to autofit as well as trying if the row height was <= 13 to autofit and neither of those worked.

Double clicking to collapse the rows still works just fine on the computers of people. How should I move forward? Any help would be greatly appreciated! Please and thanks

 

[mikerickson]

You might try something like this.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Static Flag As Boolean
    With Target
        If Flag Then
            .EntireRow.AutoFit
        Else
            .RowHeight = 12.75
        End If
    End With
    Cancel = True
    Flag = Not Flag
End Sub

Some times the Flag variable won't match what is on the sheet. If that happens, the user will think that they didn't double click properly, double click it again and the sheet will be the way that they want it.

 

[sjliang]

Thank you, I'll try that. What does the Flag variable do exactly?

 

[mikerickson]

Flag is a static variable, so the routine "remembers" what it did the last time it was run, AutoFit or FixedHeight.
So, this time it will do the other thing.(it "forgets" if the workbook is closed)

 

[sjliang]

got it. Thank you soooo much for your help!