asked on

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?

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

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

Start Free Trial

Martin Liss

I've attached an example.
Q-28516639.xlsx

-Polak

ASKER

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

Martin Liss

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

-Polak

ASKER

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.

Martin Liss

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

Martin Liss

BTW when using the above code the shape looks better IMO if it has a solid fill.

Martin Liss

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

-Polak

ASKER

Thanks Martin posted a follow up here if you'd like to participate running into some kind of permission error.

https://www.experts-exchange.com/questions/28516937/Change-Color-and-Style-of-Navigation-Buttons-when-Worksheets-are-Activated.html