Super video Victor. Glad I stumbled onto your channel. I really like your delivery. Now here is the challenge: How do I do the same things in LibreOffice/Calc? Though probably not the vba part.
I'm happy you like my delivery! One of the big strengths of Excel is the ability to automate and enhance with VBA. Most Excel users ignore VBA, but I think it's one of the most useful features in Excel. Re: your challenge, sadly I've never used LibreOffice so I can't advise you on how to do this. Good luck with looking for a solution.
@@planningpedia yes, if you know the name of the other workbook, you can specify the worksheets in that workbook. Use Workbooks (index), where index is the workbook name,. e.g. Workbooks("Name_Of_Other_Workbook.xlsx")
Sure! To position the back navigation button check out the tutorial at 25:45. There is a line of code to add the button, and you can position it at the same time. ' Add the button (shape, X, Y, Width, Height) Set btn = ws.Shapes.AddShape(msoShapeRoundedRectangle, 5, 5, 75, 30) Change the X and Y from 5, 5 to other values to position the top left corner where you want. And change the Width and Height from 75, 30 to other values to resize as you like.
This was a great video, however, that VBA is very complicated. I do know excel very well, just not the VBA stuff. I was wondering if there is a way to do the hyperlink back and forth if you just did the transpose part to get the sheet names? I have 193 or so sheets in one of my excel sheets and would like to go back and forth but without all the VBA coding, except for the VBA for hyperlinking. Thank you
Thanks for your question. Yes, I understand that VBA code is overwhelming if you haven't learned it before. I looked for a formula-only approach. But I haven't found a method with Formulas that works to create a list of sheet names that are hyperlinked. I tried wrapping the HYPERLINK() function around the GET.WORKBOOK formula, but that doesn't work because it only hyperlinks to the first sheet and the remaining sheets don't get links. ➡️ If anyone reading this has a way that works, please share it here! BTW if you want to learn VBA from the ground up, I have a highly rated course that takes you step by step: www.launchexcel.com/shop
I found a method from ExcelJet that could be what you're looking for: exceljet.net/formulas/link-to-multiple-sheets It uses helper columns to create a list of sheet names and cells, then uses the HYPERLINK() function to create hyperlinks. I hope this works for you!
Enjoyed the video on my TV. But really need to read the VBA macro - can you please give me link so that I can follow the video again with a hard copy of the macro in front of me.. Many thanks
Sure Robert! Here's the link to the article with full VBA code: www.launchexcel.com/list-all-sheets-in-excel-formula-and-vba Enjoy watching the video again 😄
Hi Victor, It's ZS again :) This solution is perfect. I will certainly give it a go but I need a small help. When listing the Sheets, could these be sorted A-Z? My sheet names are Alpha numeric and sorted list would be great. I have no experience of VBA and hoping to embark on the learning journey and makinng my work a bit easier.
Hey ZS! I'm working on a new VBA course and will cover projects like sorting sheets. So you'll be able to learn VBA from the ground up and automate your work. In the mean time, you can check out www.ablebits.com/office-addins-blog/alphabetize-tabs-excel/ (I'm not affiliated with them but I find their tutorial helpful)
I'm planning the new course outline now. If you give me a list of the top 3+ things you'd like to accomplish with VBA, I'll put them into the course outline. This will help me to choose what to include and to leave out. (For example: sending emails with VBA, making PPTs from Excel, automate reporting, working with charts, working with pivot tables, automate Power Query, etc.)
Interesting, I tried this but didn't work for me. What happens when you do this? I did find ALT W N → New window Source: www.reddit.com/r/excel/comments/jrkhr1/tired_of_flitting_back_and_forth_within_tabs/
If this is an error with get.workbook(1), you might need to check if you allowed macros to work. *Q&A on listing sheet names coming up as block:* answers.microsoft.com/en-us/msoffice/forum/all/excel-list-sheet-names-coming-up-as-blocked/2167f4df-bff2-470b-96be-3797267a9195 *Microsoft page on how to correct a **#BLOCKED**! error:* support.microsoft.com/en-us/office/how-to-correct-a-blocked-error-13be117b-92e4-400a-a215-aa59d37d6e7c
No offense, but from the get-go, i don't like the idea of VBA. In the last 7 or 8 years, i dont remember having to use it even once given the level of abstract programming that can be done with the tools and data structures accessible on an excel spreadsheet. Even writing a bona-fide stochastic simulation or programming a statistical analysis package.
Thanks for your comment, no offense taken 😀. I agree, VBA isn't for everyone. But those who use VBA know it has powerful use cases. Particularly in report automation and controlling other Office applications. I think those are the areas where VBA shines.
Great video, Victor. Thanks a lot!! In my situation, the GET.WORKBOOK function is resulting in the "#NAME?" error. I'm using Excel 365, do you have any idea what could be going on?
hi @eduartopimentel I suggest you try enabling Excel 4.0 macros. That might fix the issue. Here is Microsoft's support page: support.microsoft.com/en-gb/office/working-with-excel-4-0-macros-ba8924d4-e157-4bb2-8d76-2c07ff02e0b8 ➡️ Steps: 1. Click the File tab, and then click Options. 2. Click Trust Center, and then click Trust Center Settings. 3. Click Macro Settings, and then select the Enable Excel 4.0 macros when VBA macros are enabled check box. You can then select Disable VBA macros with notification or Enable all macros (not recommended; potentially dangerous code can run). 4. Click File Block Settings and then, under File Type, select Excel 4 MacroSheets and make sure that the check box for Open is selected. 5. Under Open behavior for selected file types, click Open selected file types in Protected View and allow editing. 6. Click OK twice.
*❤ CHAPTERS:*
00:00 - Introduction
00:57 - Level 1: Manual Tricks for Listing Sheets
01:39 - Level 2: Use Formulas for Sheet Listing
05:50 - Level 3: Automate with VBA
10:51 - Level 4: Enhance VBA with New Features
13:54 - Level 5: Error Checking and Improvements
16:11 - Level 6: Optimize Sheet List Display
18:04 - Level 7: Add Padding for Readability
22:07 - Level 8: Create Clickable Sheet Names
24:26 - Level 9: Add Back Buttons for Easy Navigation
27:03 - Level 10: Automate Sheet List Refresh
29:32 - Conclusion and Resources
I am learning here thanks for the video. Learning is a virtue. Let's keep learning
Welcome back Victor - it's nice to see some new videos from you.
Thanks Mark!
Thank you Victor for the excellent video.
Hey Mark, thanks for the comment 😄
Super video Victor. Glad I stumbled onto your channel. I really like your delivery. Now here is the challenge: How do I do the same things in LibreOffice/Calc? Though probably not the vba part.
I'm happy you like my delivery! One of the big strengths of Excel is the ability to automate and enhance with VBA. Most Excel users ignore VBA, but I think it's one of the most useful features in Excel.
Re: your challenge, sadly I've never used LibreOffice so I can't advise you on how to do this. Good luck with looking for a solution.
Great help to learners.. can we make list from another workbook also?
@@planningpedia yes, if you know the name of the other workbook, you can specify the worksheets in that workbook. Use Workbooks (index), where index is the workbook name,. e.g. Workbooks("Name_Of_Other_Workbook.xlsx")
Excellent video, especially the boss level part
Thanks for your comment. Glad you like the boss level 👍
Hai Victor,,.. excellent video...
if you could just advise on how to insert macro command on how to position the Navigation button
thank you
Sure! To position the back navigation button check out the tutorial at 25:45.
There is a line of code to add the button, and you can position it at the same time.
' Add the button (shape, X, Y, Width, Height)
Set btn = ws.Shapes.AddShape(msoShapeRoundedRectangle, 5, 5, 75, 30)
Change the X and Y from 5, 5 to other values to position the top left corner where you want.
And change the Width and Height from 75, 30 to other values to resize as you like.
Thanks A lot
You're welcome 😄
Impressive. Thanks for the video
Sure, glad you like it
Thanks a lot !!!
You are welcome!
Nice video.
Please keep us informed when the download file is available.
Thank you.
Thanks Rudi! Yes I'll get the download file ready with the article. Links coming soon 😄
This was a great video, however, that VBA is very complicated. I do know excel very well, just not the VBA stuff. I was wondering if there is a way to do the hyperlink back and forth if you just did the transpose part to get the sheet names? I have 193 or so sheets in one of my excel sheets and would like to go back and forth but without all the VBA coding, except for the VBA for hyperlinking. Thank you
Thanks for your question. Yes, I understand that VBA code is overwhelming if you haven't learned it before.
I looked for a formula-only approach. But I haven't found a method with Formulas that works to create a list of sheet names that are hyperlinked.
I tried wrapping the HYPERLINK() function around the GET.WORKBOOK formula, but that doesn't work because it only hyperlinks to the first sheet and the remaining sheets don't get links.
➡️ If anyone reading this has a way that works, please share it here!
BTW if you want to learn VBA from the ground up, I have a highly rated course that takes you step by step: www.launchexcel.com/shop
I found a method from ExcelJet that could be what you're looking for: exceljet.net/formulas/link-to-multiple-sheets
It uses helper columns to create a list of sheet names and cells, then uses the HYPERLINK() function to create hyperlinks. I hope this works for you!
@@launchexcelThank you for the help, I will look into it.
Enjoyed the video on my TV. But really need to read the VBA macro - can you please give me link so that I can follow the video again with a hard copy of the macro in front of me.. Many thanks
Sure Robert! Here's the link to the article with full VBA code:
www.launchexcel.com/list-all-sheets-in-excel-formula-and-vba
Enjoy watching the video again 😄
Hi Victor, It's ZS again :) This solution is perfect. I will certainly give it a go but I need a small help. When listing the Sheets, could these be sorted A-Z? My sheet names are Alpha numeric and sorted list would be great. I have no experience of VBA and hoping to embark on the learning journey and makinng my work a bit easier.
Hey ZS! I'm working on a new VBA course and will cover projects like sorting sheets. So you'll be able to learn VBA from the ground up and automate your work.
In the mean time, you can check out www.ablebits.com/office-addins-blog/alphabetize-tabs-excel/ (I'm not affiliated with them but I find their tutorial helpful)
@launchexcel thanks Victor, I will check the link over the weekend. Also when will you be starting the course?
I'm planning the new course outline now.
If you give me a list of the top 3+ things you'd like to accomplish with VBA, I'll put them into the course outline. This will help me to choose what to include and to leave out.
(For example: sending emails with VBA, making PPTs from Excel, automate reporting, working with charts, working with pivot tables, automate Power Query, etc.)
It would be nice if the pages were sorted from A to Z or Smallest to Largest
Thanks for the suggestion. I'll add that to my list of videos to make. I agree it's helpful to have the worksheets sorted.
Level 11 - Alt w k
Interesting, I tried this but didn't work for me. What happens when you do this?
I did find ALT W N → New window
Source: www.reddit.com/r/excel/comments/jrkhr1/tired_of_flitting_back_and_forth_within_tabs/
@@launchexcel
Opens Navigation Pane
can be found View | Show
saved the workbook as new workbook but now I just get #BLOCKED! as a result instead of the sheet name.
If this is an error with get.workbook(1), you might need to check if you allowed macros to work.
*Q&A on listing sheet names coming up as block:*
answers.microsoft.com/en-us/msoffice/forum/all/excel-list-sheet-names-coming-up-as-blocked/2167f4df-bff2-470b-96be-3797267a9195
*Microsoft page on how to correct a **#BLOCKED**! error:*
support.microsoft.com/en-us/office/how-to-correct-a-blocked-error-13be117b-92e4-400a-a215-aa59d37d6e7c
No offense, but from the get-go, i don't like the idea of VBA. In the last 7 or 8 years, i dont remember having to use it even once given the level of abstract programming that can be done with the tools and data structures accessible on an excel spreadsheet. Even writing a bona-fide stochastic simulation or programming a statistical analysis package.
Thanks for your comment, no offense taken 😀.
I agree, VBA isn't for everyone. But those who use VBA know it has powerful use cases. Particularly in report automation and controlling other Office applications. I think those are the areas where VBA shines.
too complicated
Thanks for your feedback. If you need something easier, I suggest starting with a beginner's Excel tutorial.
Great video, Victor. Thanks a lot!!
In my situation, the GET.WORKBOOK function is resulting in the "#NAME?" error. I'm using Excel 365, do you have any idea what could be going on?
hi @eduartopimentel I suggest you try enabling Excel 4.0 macros. That might fix the issue.
Here is Microsoft's support page: support.microsoft.com/en-gb/office/working-with-excel-4-0-macros-ba8924d4-e157-4bb2-8d76-2c07ff02e0b8
➡️ Steps:
1. Click the File tab, and then click Options.
2. Click Trust Center, and then click Trust Center Settings.
3. Click Macro Settings, and then select the Enable Excel 4.0 macros when VBA macros are enabled check box. You can then select Disable VBA macros with notification or Enable all macros (not recommended; potentially dangerous code can run).
4. Click File Block Settings and then, under File Type, select Excel 4 MacroSheets and make sure that the check box for Open is selected.
5. Under Open behavior for selected file types, click Open selected file types in Protected View and allow editing.
6. Click OK twice.