If you only have 2 sheets (2 tabs) in Excel 2010, default display seems counterintuitive. Way to fix?

[thankyou]

I often have 2 sheets on a workbook and I usually add color and name that, perhaps like most of us. But I feel that, when you only have 2 tabs (not more), it feels counterintuitive the way Excel (2010) shows you a color-filled tab of the sheet that you're not working on, which makes you feel that you're on that other sheet.

I've uploaded 2 screenshots to illustrate the point, imho:

"Quick, which sheet are you on. Example 1 of 2"
and
"Quick, which sheet are you on. Example 2 of 2"

http://we.tl/bIpbOGIXUr

When you're looking at an entire sheet and you have a purple a non-filled color tab vs a filled color tab staring at you, seems to me the more intuitive display rule, for 2 sheets only, would be that "you're currently on the color-filled tab". I realize this wouldn't work for more than 2 tabs.

Just wondering if anyone else feels the same? To be honest, it's kind of bothered me for years. And is there anyway to quickly configure all my future workbooks so that the tab behavior (for 2 sheets only) is opposite, and shows the sheet that I'm actually on as the fully-filled color tab?

That would be truly awesome.

 

[mikerickson]

This illustrates the different impressions of "intuitive". When I color a sheet tab, I want it to remain that color so I can quickly find and click on "the red one". Changing color of the selected sheet tab seems to me to be unnecessary visual clutter.

But to get the active sheet to be the one with the colored tab, you could put this code in the ThisWorkbook code module of the workbook in question.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Sh.Tab.Color = RGB(0, 0, 255)
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Sh.Tab.Color = RGB(255, 255, 255)
End Sub

(That reminds me of one of my college math professors. One day he came into the lecture hall, wrote an equation on the board and said "Its obvious ..." then paused, thought a moment and left the room muttering to himself. He came back 20 minutes later and said "I was right! It IS obvious.")

 

[thankyou]

[QUOTE(That reminds me of one of my college math professors. One day he came into the lecture hall, wrote an equation on the board and said "Its obvious ..." then paused, thought a moment and left the room muttering to himself. He came back 20 minutes later and said "I was right! It IS obvious.")[/QUOTE]

Exactly, I'm always interested in others' thoughts and perspectives. I like the fact that when he first said, "It's obvious!" that he might not have solved the problem had he not actually verbalized it his aha moment. To me, that's the cool part. And yeah, the idea that the more we know, the more we realize we don't actually know squat.

Mike, this is probably a common question, but is there some magical way to set a VBA for all future workbooks that you might create? I'm guessing no since you'd probably have to hack excel itself. By the time I paint the fence red, I remember its color (to keep with the philosophical theme).

Thanks for taking notice and for helping out. I really appreciate you.

 

[mikerickson]

For event code like this, I use the Personal Macro Workbook to create Application level event code.

' in Personal Macro Workbook's ThisWorkbook code module

Public WithEvents App As Application

Private Sub App_SheetActivate(ByVal Sh As Object)
    Sh.Tab.Color = RGB(0, 0, 255)
End Sub

Private Sub App_SheetDeactivate(ByVal Sh As Object)
    Sh.Tab.Color = RGB(255, 255, 255)
End Sub

Private Sub Workbook_Open()
    Set App = Me.Application
End Sub

 

[thankyou]

Wow. That sounds quite handy. Mike, would you kindly provide some nav steps on how or where I could access or create that PMW?

Hope you have an awesome day.

Kind regards,

Bob