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
LVL 1
John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
Try:

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

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

Kevin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Patrick MatthewsCommented:
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 CarneyReliability Business Tools Analyst IIAuthor Commented:
A) Works great.
B) How the hell did you answer me so fast?!!!

Thanks, Kevin


John
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

zorvek (Kevin Jones)ConsultantCommented:
I have to be fast or Patrick will get to you first ;-)

Kevin
John CarneyReliability Business Tools Analyst IIAuthor Commented:
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 CarneyReliability Business Tools Analyst IIAuthor Commented:
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 MatthewsCommented:
John,

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

Regards,

Patrick
John CarneyReliability Business Tools Analyst IIAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.