Yet Again More Excel Help - Button to hide and show colums | Off Topic | unofficial empeg BBS

Quick Links: Empeg FAQ | RioCar.Org | Hijack | BigDisk Builder | jEmplode | emphatic
Repairs: Repairs

You are not logged in. [Log In] empegbbs.com » Forums » General Discussion » Off Topic » Yet Again More Excel Help - Button to hide and show colums

Topic Options

#332091 - 14/04/2010 19:29 Yet Again More Excel Help - Button to hide and show colums
tfabris carpal tunnel Registered: 20/12/1999 Posts: 31604 Loc: Seattle, WA	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? _________________________ Tony Fabris
Top

#332093 - 14/04/2010 20:28 Re: Yet Again More Excel Help - Button to hide and show colums [Re: tfabris]
tfabris carpal tunnel Registered: 20/12/1999 Posts: 31604 Loc: Seattle, WA	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 _________________________ Tony Fabris
Top

#332095 - 14/04/2010 21:08 Re: Yet Again More Excel Help - Button to hide and show colums [Re: tfabris]
tfabris carpal tunnel Registered: 20/12/1999 Posts: 31604 Loc: Seattle, WA	No, that's still not working. In a larger, more complex spreadsheet, I get the same problem, even without the WITH statement. Any ideas? _________________________ Tony Fabris
Top

#332096 - 14/04/2010 21:18 Re: Yet Again More Excel Help - Button to hide and show colums [Re: tfabris]
tfabris carpal tunnel Registered: 20/12/1999 Posts: 31604 Loc: Seattle, WA	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. _________________________ Tony Fabris
Top

#332097 - 14/04/2010 21:33 Re: Yet Again More Excel Help - Button to hide and show colums [Re: tfabris]
tfabris carpal tunnel Registered: 20/12/1999 Posts: 31604 Loc: Seattle, WA	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. _________________________ Tony Fabris
Top

#332099 - 14/04/2010 21:48 Re: Yet Again More Excel Help - Button to hide and show colums [Re: tfabris]
wfaulk carpal tunnel Registered: 25/12/2000 Posts: 16706 Loc: Raleigh, NC US	Glad we could help. Probably a timeout somewhere. _________________________ Bitt Faulk
Top

#332107 - 15/04/2010 00:05 Re: Yet Again More Excel Help - Button to hide and show colums [Re: wfaulk]
gbeer carpal tunnel Registered: 17/12/2000 Posts: 2665 Loc: Manteca, California	The descriptive text could have been added as comments to the cells. _________________________ Glenn
Top

View All Topics