Duplicate Excel ActiveX Command Buttons across multiple sheets

Easy question regarding ActiveX Command Buttons. I'm having to use ActiveX Buttons for the first time in a Navigation pane because I would like the end-user to know which worksheet they are on by changing the color of the buttons that correspond to the Active worksheet.

I've never used ActiveX buttons and wanted to know if there is anything wrong with copy and pasting these navigation buttons across multiple sheets then renaming them to have the same name so I don't have to have a separate onClick sub for each button if the purpose of the duplicated button is the same across all sheets they are copied to...

Also what is the purpose of the =EMBED("Forms.CommandButton.1","") in the formula bar and should that be renamed to whatever I have changed the button (Name) to?
LVL 1
-PolakAsked:
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.

Martin LissOlder than dirtCommented:
It's not a good idea to use any ActiveX controls on a sheet since they were not designed for that purpose, but rather for forms (UserForms). The ActiveX controls have the bad habit of resizing themselves. You can instead use a rectangular shape, which you can format any way you want and then assign a macro to it.
0

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
Martin LissOlder than dirtCommented:
I've attached an example.
Q-28516639.xlsx
0
-PolakAuthor Commented:
Okay so a couple of things...

1.

With rectangles would I have to have a unique name for each rectangle or can I get away with having duplicated rectangles with the same names across multiple sheets and just a single onclick sub for that rectangle?

2.

I mentioned wanting to change the color of a navigation button to identify it to the active sheet the end-user is on, I assume one can do this with a rectangle too. However, can you make the rectangle "feel" like a button before it switches to the active sheet color. Basically, a onclick event to first have it looks like a depressed button then change the color to indicate that it has been toggled "on"....

3.

Is you above statement still true if you make the AutoSize property "False"? on activeX controls?
PS your attachment didn't have any VBA don't know if that was intentional
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Martin LissOlder than dirtCommented:

1. You can have duplicated rectangles with the same name across multiple sheets

2. Yes you can make it any color you want plus other effects if you so desire. However to make it look like a depressed button might take some work but why bother since you are I assume immediately taken away from the current sheet. Another option would be to use the Form control (non-ActiveX) command button and vary the font color.

3. Yes.


Yes I was aware of that, but I didn't think you needed the code, but here is the macro associated with that button.
Sub PrintSheet()
Application.SendKeys "^{p}"
End Sub

Open in new window

0
-PolakAuthor Commented:

2.

I will have to play with it a bit to see how smooth it can look without the "depressed state" just want it to still feel like a button
Thx didn't know if you put something into the VBA in terms of changing the button style or something, I will post a follow-on questions for that if needed.
0
Martin LissOlder than dirtCommented:
I just found this on the web. Add the code to a module and assign the 'Test' macro to the rectangle.
Option Explicit

Sub PressDown(MyButton As Shape)
'
' PressDown Macro
'
    With MyButton.ThreeD
        .BevelTopType = msoBevelSoftRound
        .BevelTopInset = 12
        .BevelTopDepth = 4
    End With
    DoEvents
    
End Sub
Sub PressUp(MyButton As Shape)
'
' PressUp Macro
'
    With MyButton.ThreeD
        .BevelTopType = msoBevelCircle
        .BevelTopInset = 6
        .BevelTopDepth = 6
    End With
    DoEvents
    
End Sub
Sub Test()

    Dim shpButton As Shape
    
    Set shpButton = ActiveSheet.Shapes(Application.Caller)
    
    PressDown shpButton
    
    MsgBox "Do some code stuff"
    
    PressUp shpButton
    
End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
BTW when using the above code the shape looks better IMO if it has a solid fill.
0
Martin LissOlder than dirtCommented:
I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
-PolakAuthor Commented:
Thanks Martin posted a follow up here if you'd like to participate running into some kind of permission error.

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28516937.html
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.