Learn how to have sheet names change when selected or deselected. This uses a simple lookup function and a pair of event-driven macros. Code can be copied from the sample file (link below). File download link: www.bcti.com//...
This is a cool trick. When I create a workbook with more than 3 tabs I create a sheet with an index and links that jump to cell A1 on each sheet. If I’m using cut off names I have a column with the full name on the Index sheet.
I’m afraid I disagree with all the comments based on the two click rule. It should only take you two clicks to get from any one sheet to any other sheet - regardless of size. You should have your first sheet as a Main Menu with internal navigation to all sheets. Each sheet should also be linked back to the Main Menu. Two clicks take you from any sheet to any other sheet. This avoids infinite scrolling.
You are absolutely right. I couldn’t agree more. This was merely an exercise in creativity and brining the concept of event-driven macros to people’s attention. Thanks for taking the time to watch and comment.
Unfortunately, no. I looked into that as I thought it would be neater to have the tab name changed just by hovering over it. Excel has no mechanism to detect the presence of a mouse pointer when hovering over a sheet tab. Thanks for watching.
Or you could leave the sheet names and just right-click on the tab navigation buttons to get a proper list... I'm a fan of vba and have been for years, but as soon as you introduce it (in a corporate environment) you're in for a world of hurt
I agree that it's not exactly a Nobel Prize winning idea. It was just an idea that popped into my head one day and I wanted to see if it could be done. The idea may, however, spark someone else's imagination in solving an actual problem. Thanks for watching.
The only “bad” thing about this for me is that I retire on March 1st and won’t have the need to use it after that! I have always been a fan of tricks like this all the way back to Lotus 1-2-3.
My sheet names are codes which are C01 through to C30. I have created a list C01 to C30 in column A, and a list of the different names in column B. Copied and pasted your code and altered slightly the row numbers as suggested. Mostly it works but on several nothing changes and on others it shows the names, not the codes. I'll do some debugging here but very odd. Liked the idea very much and once I've sussed this issue out I will use in many of my spreadsheets.
How, exactly, did you get the full sheet name to appear in the first couple of rows of each sheet? Is this done with the VBA code you shared or, is there another step needed to accomplish this? I like keeping sheet names as short as possible for the sake of clearer formula writing, so this technique would really come in handy, especially if I can get the full sheet name to display in the 2nd or 3rd row as a title.
You can use the SHEET function to return the sheet's number in the workbook. ex: =SHEET() This could then be used in an INDEX function to return a word from an existing list. ex: =INDEX(List!A1:A100, SHEET()) That's just an idea off the top of my head. I'm sure there's a lot of creative ways to use this.
Thank you for your reply and, like so many of my fellow followers of your work, I think you're amazing, and I will definitely start "retro-fitting" my workbook with this feature! 🙏@@bcti-bcti
This is some kind of black magic f...kery! JK. So simple and yet so elegant. I'm absoluetly loving this channel! The power query series has been incredibly helpful, thank you.
Be very careful as this can mess up formulas that aggregate across sheets by changing sheets names. Esp if the workbook is used and edited by multiple users concurrently. This looks more like a tool in search of a problem. You could always put an index page with links to each sheet and whatever meta data you want as the 1st page sheet in a book. Ctrl+Left click jumps to that sheet. Will not mess with formulas.
No doubt about it, this is a great. Would be perfect if I can refer to a Table Col instead of a cell range so new sheet names can be automatically populate without having to modify the code repeatedly. How do we specify Table Col in "Visual Basic" code?
I agree, but you could just refer to either the entire column (ex: “List!A:A”), or just set the range to something you don’t ever expect to reach (ex: “List!A1:A1000”). Thanks for watching.
As soon as you described it my immediate thought was that would be so easy to do. Then I wondered why I had never thought of actually doing it! Very clever idea. Thank you. I know a spreadsheet where this idea will be implemented later today.
This trick has a potential major flaw of the hidden list is subject to changes in the future. For that, it would be better to format the list as a table and refer to the column names directly in the formulas. Any new or deleted rows in the list works then not require modifying the code, and moving the table itself by inserting leading rows or columns would not matter to the formulas.
One would think that using a proper Excel Data Table would be a better strategy, but I think you'll find, with a bit of testing, that this introduces an entirely new set of problems and shortcomings. My guess is that using a formula that discovers the length (i.e., height) of the table would be needed. Like another MATCH function that searches the entire column (A:A) for something that will never be found (99^99), and performs this as an Option 1 (closest without going over) type of search. Thanks tor contributing to the discussion.
