Solved

Change Color and Style of Navigation Buttons when Worksheets are Activated

Posted on 2014-09-11
14
188 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 49

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 49

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 49

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 49

Expert Comment

by:Rgonzo1971
ID: 40319870
No Idea
0
 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

773 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