Link to home
Start Free TrialLog in
Avatar of -Polak
-PolakFlag for United States of America

asked on

Change Color and Style of Navigation Buttons when Worksheets are Activated

Please see the attached for an illustration of what I'm trying to do. Focus on only the Standard Mode and Manual Mode buttons.

I've been advised that using rectangle shapes with assigned macros is preferable to using ActiveX buttons when using worksheets and wanting to change the colors to of the button to reflect the active sheet. Problem with rectangles is I would still want to retain the "feel" of a button, especially if the rectangle doesn't actually call on a worksheet (eg User Guide will ultimately open a PDF). Basically, I'd like the rectangle to change on PressDown to look depressed. Then, on release to return to its current formatting. Lastly, if the button is calling another sheet have it turn Green when that sheet is active so the end-user knows where they are.

I've pasted something I was given but can't get to work with the style/formatting that I want:
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

Naviation-Buttons-EE-Safe.xlsm
SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of -Polak

ASKER

Cool nearly there see the attachment, noticed two issues.

1. You need to double-click "Manual Mode" to have the button appear depressed. I'm fine with either it looking depressed onClick or depressed while its green, doesn't matter to me.

2. Whenever Standard Mode is activated an error occurs.
Naviation-Buttons-EE-Safe.xlsm
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of -Polak

ASKER

I get a Run-time error '70': Permission Denied whenever

"shp.ThreeD.LightAngle = 0" attempts to run.

Perhaps that's also what's causing me to have to double-click?
Avatar of -Polak

ASKER

Deleted the code in ThisWorkbook and placed your new code in the NavigationButtons Module and still getting a permission denied error when attempting to run the "shp.ThreeD.LightAngle = 0"; however, now the error occurs going from Standard mode to Manual.

See the attached.
Naviation-Buttons-EE-Safe.xlsm
No Idea
Since these "buttons" are used only for navigation, why don't you consider attaching Hyperlinks to them and avoiding coding altogether?  I've attached a modified version of your workbook - no code, but added hyperlinks to the first four buttons and added an additional two sample sheets.

The visual behavior is identical to the coded version (green, "pressed" button when on sheet)

Regards,
-Glenn
EE-Naviation-Buttons-EE-Safe.xlsm
Avatar of -Polak

ASKER

Couple of reasons:
First, I wanted a uniform navigation pane that I could paste across what will end up being somewhere around 15-20 separate worksheets. Ergo, if I have to make a change to the navigation pane/UI later in development copy and pasting becomes easier if I don't have to recolor the selected button each time.

Second, I guess I just wanted to maintain the "feel" of holding down a button onclick and thought it was a little more of an elegant a solution than simply coloring the relevant button on each worksheet.

I think rogonzo's code is pretty nifty, is there any reason why that would cause a permission error?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of -Polak

ASKER

Thanks Glenn. As a note, I did try to run rogonzo's solution in excel 2013 and I did not have a permission error. So for some reason the dropdown arrow is only only effected in excel 2007.... not sure why that would be.

However, your new code works in both 2013 and 2007. I will take a closer look at the solution in the AM, thanks again!
I ran this in Excel 2010 and got the permission error.  Try clicking on cell M2 in any of the first two sheets so that the drop arrow appears, then go back and forth using the navigation buttons and see if you get the error again.

-Glenn
Avatar of -Polak

ASKER

Glenn that did not help, but I figured out that it was the 65 hidden buttons with a height of zero that you mentioned above. Remove those from the manual mode worksheet and Rgonzo's code worked just fine. Guess excel doesn't like to change the light angle on hidden buttons with a 0 height.

However, I will end up using your code to avoid any issues if I do end up using more shapes on the sheet further along in development. Thanks for being a sleuth on this one.
Avatar of -Polak

ASKER

Thanks to both experts.