Solved

Change Color and Style of Navigation Buttons when Worksheets are Activated

Posted on 2014-09-11
14
182 Views
Last Modified: 2014-09-13
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
0
Comment
Question by:-Polak
  • 7
  • 4
  • 3
14 Comments
 
LVL 48

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 250 total points
ID: 40319044
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
 
LVL 1

Author Comment

by:-Polak
ID: 40319303
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
 
LVL 48

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 250 total points
ID: 40319771
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
 
LVL 1

Author Comment

by:-Polak
ID: 40319800
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
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 40319820
0
 
LVL 1

Author Comment

by:-Polak
ID: 40319865
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
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 40319870
No Idea
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40320216
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
 
LVL 1

Author Comment

by:-Polak
ID: 40320452
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
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 250 total points
ID: 40320509
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
 
LVL 1

Author Comment

by:-Polak
ID: 40320521
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40320528
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
 
LVL 1

Author Comment

by:-Polak
ID: 40320912
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
 
LVL 1

Author Closing Comment

by:-Polak
ID: 40320914
Thanks to both experts.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

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.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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…

743 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

15 Experts available now in Live!

Get 1:1 Help Now