Yet Again More Excel Help - Button to hide and show colums

Posted by: tfabris

Yet Again More Excel Help - Button to hide and show colums - 14/04/2010 19:29

I added a button to my worksheet. The button is supposed to hide and unhide columns which contain a paragraph of descriptive text.

When the columns are shown, I need to have the text-wrap turned on so you can see everything. When the columns are hidden, they need the wrap off to keep the data in the document readable.

Code:
Sub Button1_Click()
    With Worksheets("My Worksheet Name").Columns("D:E")
        If .Hidden Then
            .Hidden = False
            .WrapText = True
        Else
            .WrapText = False
            .Hidden = True
        End If
    
    End With
End Sub


Within the "IF" statement, sometimes only the first of the paired statements gets executed. Sometimes they both get executed. I can see this happening in the step-by-step debugger.

I don't think it's because of the malleable state of the ".Hidden" property, because I tried to copy that into to a variable at the top of the function and then execute a different IF statement based on the variable, and I got the same results.

What's up here?
Posted by: tfabris

Re: Yet Again More Excel Help - Button to hide and show colums - 14/04/2010 20:28

Interesting. It seems to work if I get rid of the WITH statement and change the order of instructions:
Code:
Sub Button1_Click()
    Set MyColumns = Worksheets("My Worksheet Name").Columns("D:E")
        If MyColumns.Hidden Then
            MyColumns.Hidden = False
            MyColumns.WrapText = True
        Else
            MyColumns.Hidden = True
            MyColumns.WrapText = False
        End If
End Sub
Posted by: tfabris

Re: Yet Again More Excel Help - Button to hide and show colums - 14/04/2010 21:08

No, that's still not working. In a larger, more complex spreadsheet, I get the same problem, even without the WITH statement.

Any ideas?
Posted by: tfabris

Re: Yet Again More Excel Help - Button to hide and show colums - 14/04/2010 21:18

In the larger spreadsheet, even if I separate it into two buttons and don't even use an IF statement, only the first of the paired statements gets executed:

Code:
Sub OptionButton2_Click()
    
    Set MyColumns = Worksheets("My Worksheet Name").Range("My Range").Columns
    MyColumns.Hidden = False
    MyColumns.WrapText = True

End Sub
Sub OptionButton3_Click()

    Set MyColumns = Worksheets("My Worksheet Name").Range("My Range").Columns
    MyColumns.WrapText = False
    MyColumns.Hidden = True

End Sub


The problem happens everywhere I'm trying to turn OFF text wrapping in those cells. It's like the act of turning off text wrapping is choking Excel somehow. I turn it off and there's a long pause in the script execution, and the next statement never executes. So, in the example above, it turns off the text wrap but never hides the columns.
Posted by: tfabris

Re: Yet Again More Excel Help - Button to hide and show colums - 14/04/2010 21:33

Oh, that's rich. If I wrap the entire thing in this:

Code:
    Application.ScreenUpdating = False

(the rest of my function)

    Application.ScreenUpdating = True


... then it seems to work.

Arg.
Posted by: wfaulk

Re: Yet Again More Excel Help - Button to hide and show colums - 14/04/2010 21:48

Glad we could help. wink

Probably a timeout somewhere.
Posted by: gbeer

Re: Yet Again More Excel Help - Button to hide and show colums - 15/04/2010 00:05

The descriptive text could have been added as comments to the cells.