Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Toggling autofit
Hi I have a simple to do list with a cell that i am using to take notes - I'm trying to use the following macro to toggle autofit on and off for a particular cell. I have assigned the macro to a shape on the worksheet. Problem is I often have to click the shape multiple times in order for the macro to run. Any ideas why the following macro isn't working? - I think it must have something to do with my using a shape on the wks because the macro works fine when I run it from the vba editor. Sub RowAutoFit() Dim StandardHeight StandardHeight = 29 If Not Selection.RowHeight = StandardHeight Then Selection.RowHeight = StandardHeight Else Selection.Rows.AutoFit End If End Sub Thanks Richard -- rgarber50 ------------------------------------------------------------------------ rgarber50's Profile: http://www.excelforum.com/member.php...o&userid=11350 View this thread: http://www.excelforum.com/showthread...hreadid=401721 |
#2
|
|||
|
|||
I put a button from the Forms toolbar on the worksheet and assigned your macro
to it. It worked fine. I put a rectangle from the Drawing toolbar on that same worksheet and assigned your macro to it. It almost worked as good. It sure looks to me like those shapes have a harder time playing catchup when you click on them really fast--but it reacted nicely when used in a more normal fashion. You may want to try the Form's button to see if you can tell the difference. ps. If Not Selection.RowHeight = StandardHeight Then is the same as: If Selection.RowHeight < StandardHeight Then Personally, I find the second one easier to read. But you could use the equal and swap the then & else clauses. If Selection.RowHeight = StandardHeight Then Selection.Rows.AutoFit Else Selection.RowHeight = StandardHeight End If To accomplish the same thing. (Mostly personal preference.) rgarber50 wrote: Hi I have a simple to do list with a cell that i am using to take notes - I'm trying to use the following macro to toggle autofit on and off for a particular cell. I have assigned the macro to a shape on the worksheet. Problem is I often have to click the shape multiple times in order for the macro to run. Any ideas why the following macro isn't working? - I think it must have something to do with my using a shape on the wks because the macro works fine when I run it from the vba editor. Sub RowAutoFit() Dim StandardHeight StandardHeight = 29 If Not Selection.RowHeight = StandardHeight Then Selection.RowHeight = StandardHeight Else Selection.Rows.AutoFit End If End Sub Thanks Richard -- rgarber50 ------------------------------------------------------------------------ rgarber50's Profile: http://www.excelforum.com/member.php...o&userid=11350 View this thread: http://www.excelforum.com/showthread...hreadid=401721 -- Dave Peterson |
#3
|
|||
|
|||
Dave Thanks for your response. I was beginning to get the same idea as you. I also tried a form's button - which worked fine - and the more I played around with the shapes the more I began to think they needed a longer click to fire the macro - thanks for confirming that. As far as using "Not" - I started out with <, but since I am a beginner programmer, and was having trouble making the thing work, I tried using Not - I have read that in some situations its the preferred way to go. I've gone back to < which I also think is easier to read. Thanks for your help - your a generous guy with your time Dave and its greatly appreciated - once again. Regards, Richard Dave Peterson Wrote: I put a button from the Forms toolbar on the worksheet and assigned your macro to it. It worked fine. I put a rectangle from the Drawing toolbar on that same worksheet and assigned your macro to it. It almost worked as good. It sure looks to me like those shapes have a harder time playing catchup when you click on them really fast--but it reacted nicely when used in a more normal fashion. You may want to try the Form's button to see if you can tell the difference. ps. If Not Selection.RowHeight = StandardHeight Then is the same as: If Selection.RowHeight < StandardHeight Then Personally, I find the second one easier to read. But you could use the equal and swap the then & else clauses. If Selection.RowHeight = StandardHeight Then Selection.Rows.AutoFit Else Selection.RowHeight = StandardHeight End If To accomplish the same thing. (Mostly personal preference.) rgarber50 wrote: Hi I have a simple to do list with a cell that i am using to take notes - I'm trying to use the following macro to toggle autofit on and off for a particular cell. I have assigned the macro to a shape on the worksheet. Problem is I often have to click the shape multiple times in order for the macro to run. Any ideas why the following macro isn't working? - I think it must have something to do with my using a shape on the wks because the macro works fine when I run it from the vba editor. Sub RowAutoFit() Dim StandardHeight StandardHeight = 29 If Not Selection.RowHeight = StandardHeight Then Selection.RowHeight = StandardHeight Else Selection.Rows.AutoFit End If End Sub Thanks Richard -- rgarber50 ------------------------------------------------------------------------ rgarber50's Profile: http://www.excelforum.com/member.php...o&userid=11350 View this thread: http://www.excelforum.com/showthread...hreadid=401721 -- Dave Peterson -- rgarber50 ------------------------------------------------------------------------ rgarber50's Profile: http://www.excelforum.com/member.php...o&userid=11350 View this thread: http://www.excelforum.com/showthread...hreadid=401721 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can one adjust Format Row Autofit to ensure WYSIWYG when printing | Excel Discussion (Misc queries) | |||
Autofit makes rows too tall | Excel Discussion (Misc queries) | |||
Autofit makes rows too tall | Excel Discussion (Misc queries) | |||
Need code to Autofit Row Height on recalculation | Excel Discussion (Misc queries) | |||
Row Autofit Problem | Excel Discussion (Misc queries) |