*➡ List All Your Sheets Efficiently in Excel (10 Levels)* th-cam.com/video/SyGLWog59GA/w-d-xo.html *❤ Chapters in this video:* 00:00 Transform Your Workflow with a Master Sheet 01:53 Level 1: Excel Navigation Pane 05:26 Level 2: Custom Master Sheet (Manual Hyperlinks) 10:09 Level 3: Custom Master Sheet (Hyperlink Formula) 15:05 Level 4: Automate Your Master Sheet with VBA 23:04 Level 5: A. Outline the VBA code 30:45 Level 5: B. Master Sheet Constants 37:02 Level 6: Streamline Macro Testing with VB Editor 41:56 Level 7: Write the main Master Sheet VBA code 01:02:19 Level 8: Add Navigation Back Buttons 01:06:44 Level 9: Customize Colors & Layout 01:11:10 Level 10: Deploy Master Sheet in Other Workbooks 01:12:41 Excel Mastery: Beyond the Basics *🅰 Click here for the article with full VBA code:* www.launchexcel.com/link-every-worksheet-to-master-sheet *🅱 Download the sample workbooks with full VBA code in a ZIP file:* d1yei2z3i6k35z.cloudfront.net/2762499/66228d7731b77_Master-Sheet-VBA-Levels-1-to-10.zip
Want to see a specific Excel or VBA tutorial? Write a comment to make a request, and like those requests you want to upvote! I'll use your requests to guide new video creation.
I am LOVING your videos!! How you do not have more followers is beyond me!! I love your teaching style! One thing I would like to add that might be helpful is that for videos like this that are very long break them up into separate videos and put them in a playlist. I actually watch several videos while working, and I hate the fact that stopping, and skipping over might be hurting you because of a TH-cam algorithm. Just a suggestion. I do know that I will watch this from start to finish when I am off work. Please keep up the great work!
Very useful video with a lot of interesting VBA code (using constants, functions, do...loop/until) Can be used everywhere and being customized for other purposes. 👌💥
Great work and painstaking effort. Congrats Victor. I think that for some of the users the back button may not work as the FQON of the Master worksheet is hard coded in the add navigation button subroutine. An easier approach would be to remove the MasterSheet and substitue it with Me.CodeName. This should enable the navigation button on everypage work flawlessly.
@@launchexcel Your code: ' Must include 'MasterSheet' sheet codename so other sheets can find the macro ws.Shapes("btn_GoToMasterSheet").OnAction = "MasterSheet.Go_To_Master_Sheet" Replace it with the new suggestion: ' Must include 'MasterSheet' sheet codename so other sheets can find the macro ws.Shapes("btn_GoToMasterSheet").OnAction = Me.CodeName & ".Go_To_Master_Sheet" This should work.
@launchexcel Your code: ' Must include 'MasterSheet' sheet codename so other sheets can find the macro ws.Shapes("btn_GoToMasterSheet").OnAction = "MasterSheet.Go_To_Master_Sheet" My suggestion: ' Must include 'MasterSheet' sheet codename so other sheets can find the macro ws.Shapes("btn_GoToMasterSheet").OnAction = Me.CodeName & ".Go_To_Master_Sheet"
I love your teaching skills, Structured, Incremental, organised, stepwise explanation. I used to use filemaker by apple. Never used a better program than filemaker asn that is a database builder. Way above excel. but, since excel is adopted and used more widely, it's easier to share the work. With office 365 coming in, it's bringing in more and more functions. But, i would love it if you could create a sheet which can control inventory, and invoicing, and payments linked to invoicing in excel. Is it even possible. As Ive made such files in Filemaker. thanks
Thanks again for your positive comment 😄 I recommend you check out Randy Austin for his Excel Inventory Management System. I don't have anything like that. It's on the Excel for Freelancers channel: th-cam.com/video/Mh9cHoXaBug/w-d-xo.html
I'm not a Google Sheets user, I guess you can use the manual hyperlink method. See support.google.com/docs/answer/45893?hl=en&co=GENIE.Platform%3DDesktop#zippy=%2Ccreate-a-link%2Clink-to-a-range-of-cells-in-a-spreadsheet I couldn't see a way to automate this. If anyone uses Google Sheets and knows a solution, please feel free to share.
You lost me when you switched to color constants. I am learning and you jumped to it without really explaining what you are doing and what we are looking at.
Thanks for your feedback! I’m sorry for any confusion caused by the switch to color constants. Let’s break it down a bit more: When we talk about color constants in VBA, we’re referring to predefined values that represent colors. For example, vbRed, vbGreen, and vbBlue are constants for red, green, and blue, respectively. These constants make it easier to set colors without needing to know the exact RGB values. Here’s a quick explanation of what I did: 1. Color Constants: These are used to make your code more readable. Instead of typing the RGB values every time you need a color, you can use these constants. 2. Setting Cell Colors: When I set the cell colors using these constants, it was to visually differentiate parts of the master sheet for better clarity and organization. Additionally, you can define your own color constants by assigning RGB values to them. This can make your code even more readable and tailored to your needs. Here’s a simple example: Const myCustomColor As Long = RGB(121, 0, 121) ' This defines a custom purple color Now, you can use myCustomColor in your code to apply this specific color.
*➡ List All Your Sheets Efficiently in Excel (10 Levels)*
th-cam.com/video/SyGLWog59GA/w-d-xo.html
*❤ Chapters in this video:*
00:00 Transform Your Workflow with a Master Sheet
01:53 Level 1: Excel Navigation Pane
05:26 Level 2: Custom Master Sheet (Manual Hyperlinks)
10:09 Level 3: Custom Master Sheet (Hyperlink Formula)
15:05 Level 4: Automate Your Master Sheet with VBA
23:04 Level 5: A. Outline the VBA code
30:45 Level 5: B. Master Sheet Constants
37:02 Level 6: Streamline Macro Testing with VB Editor
41:56 Level 7: Write the main Master Sheet VBA code
01:02:19 Level 8: Add Navigation Back Buttons
01:06:44 Level 9: Customize Colors & Layout
01:11:10 Level 10: Deploy Master Sheet in Other Workbooks
01:12:41 Excel Mastery: Beyond the Basics
*🅰 Click here for the article with full VBA code:*
www.launchexcel.com/link-every-worksheet-to-master-sheet
*🅱 Download the sample workbooks with full VBA code in a ZIP file:*
d1yei2z3i6k35z.cloudfront.net/2762499/66228d7731b77_Master-Sheet-VBA-Levels-1-to-10.zip
Want to see a specific Excel or VBA tutorial? Write a comment to make a request, and like those requests you want to upvote! I'll use your requests to guide new video creation.
I'd love to learn and smash VBA.
I am LOVING your videos!! How you do not have more followers is beyond me!! I love your teaching style! One thing I would like to add that might be helpful is that for videos like this that are very long break them up into separate videos and put them in a playlist. I actually watch several videos while working, and I hate the fact that stopping, and skipping over might be hurting you because of a TH-cam algorithm. Just a suggestion. I do know that I will watch this from start to finish when I am off work. Please keep up the great work!
Thank you so much @Wreinie! 🥳 Let’s spread the word together and build this awesome community. Stay tuned for more fun and learning! 🚀✨
Very useful video with a lot of interesting VBA code (using constants, functions, do...loop/until)
Can be used everywhere and being customized for other purposes. 👌💥
Very good, appreciate your structured learning style, i will adopt it for teaching others
Thanks @jimmckie - I hope this structured learning style helps your students 😊
Great work and painstaking effort. Congrats Victor. I think that for some of the users the back button may not work as the FQON of the Master worksheet is hard coded in the add navigation button subroutine. An easier approach would be to remove the MasterSheet and substitue it with Me.CodeName. This should enable the navigation button on everypage work flawlessly.
Thanks for the idea! Do you have code you can share to show how you implemented "Me.CodeName"?
@@launchexcel
Your code:
' Must include 'MasterSheet' sheet codename so other sheets can find the macro
ws.Shapes("btn_GoToMasterSheet").OnAction = "MasterSheet.Go_To_Master_Sheet"
Replace it with the new suggestion:
' Must include 'MasterSheet' sheet codename so other sheets can find the macro
ws.Shapes("btn_GoToMasterSheet").OnAction = Me.CodeName & ".Go_To_Master_Sheet"
This should work.
@launchexcel
Your code:
' Must include 'MasterSheet' sheet codename so other sheets can find the macro
ws.Shapes("btn_GoToMasterSheet").OnAction = "MasterSheet.Go_To_Master_Sheet"
My suggestion:
' Must include 'MasterSheet' sheet codename so other sheets can find the macro
ws.Shapes("btn_GoToMasterSheet").OnAction = Me.CodeName & ".Go_To_Master_Sheet"
I love your teaching skills, Structured, Incremental, organised, stepwise explanation. I used to use filemaker by apple. Never used a better program than filemaker asn that is a database builder. Way above excel. but, since excel is adopted and used more widely, it's easier to share the work. With office 365 coming in, it's bringing in more and more functions. But, i would love it if you could create a sheet which can control inventory, and invoicing, and payments linked to invoicing in excel. Is it even possible. As Ive made such files in Filemaker. thanks
Thanks again for your positive comment 😄
I recommend you check out Randy Austin for his Excel Inventory Management System. I don't have anything like that.
It's on the Excel for Freelancers channel:
th-cam.com/video/Mh9cHoXaBug/w-d-xo.html
Thank you
You're welcome!
Does it work in google sheets
s
I'm not a Google Sheets user, I guess you can use the manual hyperlink method.
See support.google.com/docs/answer/45893?hl=en&co=GENIE.Platform%3DDesktop#zippy=%2Ccreate-a-link%2Clink-to-a-range-of-cells-in-a-spreadsheet
I couldn't see a way to automate this. If anyone uses Google Sheets and knows a solution, please feel free to share.
You lost me when you switched to color constants. I am learning and you jumped to it without really explaining what you are doing and what we are looking at.
Thanks for your feedback! I’m sorry for any confusion caused by the switch to color constants. Let’s break it down a bit more:
When we talk about color constants in VBA, we’re referring to predefined values that represent colors. For example, vbRed, vbGreen, and vbBlue are constants for red, green, and blue, respectively. These constants make it easier to set colors without needing to know the exact RGB values.
Here’s a quick explanation of what I did:
1. Color Constants: These are used to make your code more readable. Instead of typing the RGB values every time you need a color, you can use these constants.
2. Setting Cell Colors: When I set the cell colors using these constants, it was to visually differentiate parts of the master sheet for better clarity and organization.
Additionally, you can define your own color constants by assigning RGB values to them. This can make your code even more readable and tailored to your needs. Here’s a simple example:
Const myCustomColor As Long = RGB(121, 0, 121) ' This defines a custom purple color
Now, you can use myCustomColor in your code to apply this specific color.