*➡ Watch Next: List All Your Sheets Efficiently in Excel (10 Levels)* ua-cam.com/video/SyGLWog59GA/v-deo.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 Free Excel Workbook* Step 1: Sign up for free: go.launchexcel.com/email-signup-youtube Step 2: Log in here: courses.launchexcel.com/dashboard/en/login Step 3: Download: courses.launchexcel.com/school/course/workbook-downloads/lecture/5074882#headline-e96e5774 *TIP: Stay logged in for quick access to all FREE workbook downloads.*
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. 👌💥
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 UA-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!
Lovely video clear and concise but i do have a question, using VBA can i create a sub master sheet say for example i have one master sheet with general headings and then each general heading leads to a different master sheet that has even more headings of sheets to go through?
If I was to do something similar, I would use an Excel table and let it handle the formatting. In addition, I would not let the master sheet update automatically; instead, I would use a refresh button.
When it comes to having a master sheet with locked sheets, I guess it depends on your intended workflow. You could use VBA code to individually unlock all sheets, separately from a Master sheet. But if you have a master sheet that automatically unlocks the sheets that you click on, I'm not sure why you'd do that as it would be the same as not locking them in the first place. Or maybe I've misunderstood your requirement?
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: ua-cam.com/video/Mh9cHoXaBug/v-deo.html
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'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.
*➡ Watch Next: List All Your Sheets Efficiently in Excel (10 Levels)*
ua-cam.com/video/SyGLWog59GA/v-deo.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 Free Excel Workbook*
Step 1: Sign up for free: go.launchexcel.com/email-signup-youtube
Step 2: Log in here: courses.launchexcel.com/dashboard/en/login
Step 3: Download: courses.launchexcel.com/school/course/workbook-downloads/lecture/5074882#headline-e96e5774
*TIP: Stay logged in for quick access to all FREE workbook downloads.*
Thanks for this. Really good 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. 👌💥
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 UA-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 good, appreciate your structured learning style, i will adopt it for teaching others
Thanks @jimmckie - I hope this structured learning style helps your students 😊
Lovely video clear and concise but i do have a question, using VBA can i create a sub master sheet say for example i have one master sheet with general headings and then each general heading leads to a different master sheet that has even more headings of sheets to go through?
If I was to do something similar, I would use an Excel table and let it handle the formatting. In addition, I would not let the master sheet update automatically; instead, I would use a refresh button.
Awesome video
We have multiple sheets that are locked Would it work unlocked all sheets saving us time individually unlocking sheets?
When it comes to having a master sheet with locked sheets, I guess it depends on your intended workflow.
You could use VBA code to individually unlock all sheets, separately from a Master sheet.
But if you have a master sheet that automatically unlocks the sheets that you click on, I'm not sure why you'd do that as it would be the same as not locking them in the first place.
Or maybe I've misunderstood your requirement?
Very good and clear explanation. Nice work.
Thanks you!
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"
Appreciate this video. Thank you ❤
You are so welcome!
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:
ua-cam.com/video/Mh9cHoXaBug/v-deo.html
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.
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.