-Polak
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:
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
Naviation-Buttons-EE-Safe.xlsm
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
"shp.ThreeD.LightAngle = 0" attempts to run.
Perhaps that's also what's causing me to have to double-click?
the try to delete the code in thisWorkbook
use my new code @https://www.experts-exchange.com/questions/28516937/Change-Color-and-Style-of-Navigation-Buttons-when-Worksheets-are-Activated.html?anchorAnswerId=40319771#a40319771
use my new code @https://www.experts-exchange.com/questions/28516937/Change-Color-and-Style-of-Navigation-Buttons-when-Worksheets-are-Activated.html?anchorAnswerId=40319771#a40319771
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
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
The visual behavior is identical to the coded version (green, "pressed" button when on sheet)
Regards,
-Glenn
EE-Naviation-Buttons-EE-Safe.xlsm
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
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
-Glenn
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.
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.
ASKER
Thanks to both experts.
ASKER
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