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

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

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