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
LVL 1
-PolakAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try

'in the ThisWorkbook module

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
For Each shp In Sh.Shapes
    shp.ThreeD.LightAngle = 0
Next
End Sub

'and in NavigationsButtons

Sub BTStandardMode()

ActiveSheet.Shapes(Application.Caller).ThreeD.LightAngle = 180
'move to the StandardMode sheet
Sheets("StandardMode").Activate
Call UpdateButtonColor

End Sub

Sub BTManualMode()

ActiveSheet.Shapes(Application.Caller).ThreeD.LightAngle = 180
'move to the Manual Mode sheet
Sheets("ManualMode").Activate
Call UpdateButtonColor

End Sub

Sub UpdateButtonColor()
For Each shp In ActiveSheet.Shapes
    If "BT" & ActiveSheet.Name = shp.Name Then
        shp.Fill.ForeColor.RGB = RGB(46, 232, 32)
    Else
        shp.Fill.ForeColor.RGB = RGB(217, 217, 217)
    End If
Next
End Sub

Open in new window

Regards
0
-PolakAuthor Commented:
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
0
Rgonzo1971Commented:
1 the dbl-click effect is not desirable because it doesn't mean anything
pls use

Sub UpdateButtonColor()
For Each shp In ActiveSheet.Shapes
    If "BT" & ActiveSheet.Name = shp.Name Then
        shp.Fill.ForeColor.RGB = RGB(46, 232, 32)
    Else
        shp.Fill.ForeColor.RGB = RGB(217, 217, 217)
    End If
    shp.ThreeD.LightAngle = 0
Next
End Sub

the button remaining depressed will be useful for the ones where the active sheet does not change waiting for the macro to be finished

2 I do not have errors in standard Mode

pls explain
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

-PolakAuthor Commented:
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?
0
Rgonzo1971Commented:
0
-PolakAuthor Commented:
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
0
Rgonzo1971Commented:
No Idea
0
Glenn RayExcel VBA DeveloperCommented:
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
0
-PolakAuthor Commented:
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?
0
Glenn RayExcel VBA DeveloperCommented:
I was able to replicate the error and resolve it.  

When you select M2 in any sheet and activate the data validation, that drop arrow is another drawing shape in the workbook.  The UpdateButtonColor subroutine was trying to apply 3D shading to the drop arrow shape, causing the error.

I modified the subroutine to only affect drawing shapes beginning with "BT".  Additionally, I added a sample third sheet - UserGuide - and updated all the Worksheet_Change events to accomodate it.

Lastly, I removed the 65 hidden buttons from the ManualMode sheet (all with zero height above row 1 and then formatted the top section with white fill to remove the gridlines and visually separate the top section better.

Updated workbook attached.

Regards,
-Glenn
EE-Naviation-Buttons-EE-Safe.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
-PolakAuthor Commented:
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!
0
Glenn RayExcel VBA DeveloperCommented:
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
0
-PolakAuthor Commented:
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.
0
-PolakAuthor Commented:
Thanks to both experts.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.