Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Duplicate  Excel ActiveX Command Buttons across multiple sheets

Posted on 2014-09-11
9
Medium Priority
?
2,244 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 50

Accepted Solution

by:
Martin Liss earned 2000 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 50

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 50

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
 
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 50

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 50

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 50

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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

578 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