ฝัง
- เผยแพร่เมื่อ 28 ม.ค. 2024
- 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//wp-content/YT_D... - วิทยาศาสตร์และเทคโนโลยี
Every single video you post is awesome.
Thank you so much for saying so. I have a big smile! Cheers.
So Simple - so very useful! Going to add this to half a dozen workbooks now.
Absolutely peak scenario! I am simply amazed! Magnificent work - your content is so unique!
Thanks for watching. I try to show things that haven't been shown by 1,000 other publishers.
Extraordinarily creative! I love it, so much! Thank you for publicizing your discovery!
Thank you for wathcing!!!
Wow! Absolutely 💯 AWESOME! You are not only creative, but also generous dude! Just thinking of this idea!
Thanks. It’s a weird and mostly useless idea, but it sparks creativity for other things. Thanks for watching.
Indeed, never seen this before. Pretty slick.
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.
Awesome Sauce. Will be using as standard. Thanks for sharing.
Thanks for watching!!!
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.
Thanks so much for taking the time to watch and respond. Cheers.
insightful vid, Thank you for your time
Thank you for your time and thanks for watching
Great job, I made it without any VBA experience and it works great, also helped other people.
Glad to hear.
Simple but powerful - Thanks for sharing
Thank YOU for watching!
Brilliantly awesome, thank you!
So glad you enjoyed it. Thanks for watching.
This is supercool. I am already thinking how I can use this at work for a couple of workbooks. :) Thanks. Great idea!!
Thanks so much for watching!!!
Boy, this is genius! Why did I not think of this?! 😅
Many thanks. I can think of a few workbooks I use that would benefit from this. 👌
Fantastic!!! So glad to hear.
I will subscribe and give you a thumbs up because this is the best! I adore it and want to use this approach into a couple projects.
Thank you so much for the nice words and your subscription. It's always nice to see the number go up!!! Thanks for watching.
What a find! An immediate subscribe and bell icon!
Thank you so much!!!!
Thanks Great example.
Than you for taking the time to watch!!!
Fantastic tutorial 👌
THank you so much. And thanks for watching.
Great Brain Job....excellent....
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.
That is a much better idea. This was all just for an interesting bit of fun.
@@bcti-bcti I think it’s cool it changes the name automatically! I started using VBA back in Excel 98 and using a macro like this is very cool.
Excellent 👍👍👍👍👍
Thanks for taking the time to watch.
Genius!!
Very Nice Idea
Thanks. And thank you for taking the time to watch.
Nice!
Super!
This is incredibly useful. Too bad a lot of businesses don't allow Macro. But for personal use, absolutely!!
I agree. Macros are incredibly useful. Good thing businesses aren't putting the "smack down" on Power Query.
@@bcti-bcti, agree there. That's where I live. But I did recently discover the CELL function. Nesting MID, CELL and FIND has been useful.
Knowledge is POWER!! Especially when using Excel.@@michaelt312
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.
Best of luck in your retirement. I'm sure there's a part of all this that you'll miss. Cheers.
Although I usually jûst right click on the sheet navigâtion tab, I think your trick is amazing. Code is isimple, but the idea is not so obvious.
Yeah, the obvious is sometimes the most elusive.
Cool
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
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.
I am amazed at the number of times I've had those, "Why didn't I think of that?" moments. Thanks for watching.
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.
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.
Is there a kind of MouseOver event, so you could change the tab name before even clicking it? Would be even cooler.
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.
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.
If you can post your file so that I can download it, I'd be happy to take a look and see what I can figure out. Thanks for watching.
How would I post this file?@@bcti-bcti
I thought I answered, but can't see my comment. It was, how can I send you my spreadsheet?
You can send it as an email to training@bcti.com @@barrywood7741
2:26 And wouldn't it also help if we used Power Query to get a list of the "tab"/sheet names?
There you go! Now you’re getting creative!!!
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
Agreed. I really love the “right-click” option for the quick list. The point of this was just to show something creative. Thanks for watching.
I thought exactly the same. This is a terrible idea. Creativity isn't solving problems that don't exist.
100% on the corporate world of hurt 😂
Yes, quite likely.@@whoslacks
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.
That's great unless you have macros that refer to sheet names. I suppose you can use the sheet objects instead, but who does that?
Yeah, it has it's issues. It's a just for a bit of fun (but limited in its practicality.)
This will break any formulas that reference the sheet name
Yes, you are absolutely correct. It's just a neat thing, provided you don't have that sort of dependency. Thanks for watching.
…and no macros required. File can be saved as xlsx.
Unfortunately, not. But an alternative might be an .XLSB file (if .XLSM files are blocked by your organization.)
Logic is never " Snakeoil" skills have utility = Value as in "valuable"
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.
I agree. This was just for fun.
Didn't work. Guess it's for the desktop version of excel and not the 365 version of excel 🤷🏽♀️
I don’t believe there is any reason why this wouldn’t work in non-365 environments. Double-check your efforts. Thanks for watching.
Not sure, love the idea, but the second you click on the tab, you have a massive title with the estate name, why bother?
THis was all just an exercise in creativity. I can't say it's something I'd use often, either.
Yes you are a genius and yes it is worthless lol. No I am just kidding, this is a useful idea
HA! Yes, I recognize the value of this feature, but it is an interesting way to learn about event-driven macros (for those who are unfamiliar.)
@@bcti-bctiYrs, this video was quite interesting - especially for me who is a VBA newbie... It is interesting to see what can be done with it!