Solved

Duplicate  Excel ActiveX Command Buttons across multiple sheets

Posted on 2014-09-11
9
1,510 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 46

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 46

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 46

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 46

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 46

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 46

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

910 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

21 Experts available now in Live!

Get 1:1 Help Now