Solved

Duplicate  Excel ActiveX Command Buttons across multiple sheets

Posted on 2014-09-11
9
1,442 Views
Last Modified: 2014-09-12
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?
0
Comment
Question by:-Polak
  • 6
  • 3
9 Comments
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40317946
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40317961
I've attached an example.
Q-28516639.xlsx
0
 
LVL 1

Author Comment

by:-Polak
ID: 40318036
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40318077

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 1

Author Comment

by:-Polak
ID: 40318090

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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40318155
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40318202
BTW when using the above code the shape looks better IMO if it has a solid fill.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40318321
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
 
LVL 1

Author Comment

by:-Polak
ID: 40320111
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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now