asked on

Changing the size of a named shape on another sheet without selecting anything.

Is there a way to make these two subs below even more economical? I tried simply to eliminate < Sheets("Parties").Activate > , but that didn't work. Or is ".Activate" (unlike ".Select ") completely non-invasive already?

Sub BtnSubmFormExpand()
Sheets("Parties").Activate
[btn_Go2SubmForm].ShapeRange.Width = 819.75
End Sub

Sub BtnSubmFormCondense()
Sheets("Parties").Activate
[btn_Go2SubmForm].ShapeRange.Width = 402#
End Sub

Points start at 125 if there is.

Thanks!

John

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patrick Matthews

Hi John,

If the code is in the sheet module for Parties:

Sub BtnSubmFormExpand()
Me.[btn_Go2SubmForm].ShapeRange.Width = 819.75
End Sub

Sub BtnSubmFormCondense()
Me.[btn_Go2SubmForm].ShapeRange.Width = 402#
End Sub

Regards,

Patrick

John Carney

ASKER

A) Works great.
B) How the hell did you answer me so fast?!!!

Thanks, Kevin

John

zorvek (Kevin Jones)

I have to be fast or Patrick will get to you first ;-)

Kevin

John Carney

ASKER

Patrick,

I'm intrigued by your answer now that I see it. I deleted the 2 codes from the regular module, and put them exactly as is, but I got a "Sub or function not defined" error, when its parent macro tried to call it: Call BtnSubmFormExpand.

If i'm understanding you properly, and can make it work too, we can ask Brad to re-open the question so I can give you 125 points as well.

Thanks,

John

John Carney

ASKER

I left this part out just above, but I did in fact put them exactly as is in the sheet code pane for "Parties"

Patrick Matthews

John,

My suggestion only works if the code is in the sheet module for Parties.

Regards,

Patrick

John Carney

ASKER

Yeah, thats what I did. no "Private Sub Worksheet, etc." ... just as is in the sheet module for Parties:

Sub BtnSubmFormExpand()
Me.[btn_Go2SubmForm].ShapeRange.Width = 819.75
End Sub

Sub BtnSubmFormCondense()
Me.[btn_Go2SubmForm].ShapeRange.Width = 402#
End Sub