That was very creative! Thank you for sharing this video. I would’ve put this into a pivot chart and used the three tables as a filter at the top so I could just chose what table I wanted to see. There are other ways to set this up too but this way allows for a lot of options!
The way you are explaining/teaching is very nice. there are little bit difference between vba code showing in video and in the attached file. However its very nice vide.
Hi, thank you so much for sharing this amazing concept. Pardon me, but when I attempted to add checkboxes to the sheet, they kept appearing in each tab. Is there a way to have each checkbox appear in a tab separately? Thanks in advance.
Dear, Thanks for thanking us! You are very welcome. We are glad that you have found the concept excellent. You tried adding checkboxes instead of typical shapes. However, you found it challenging to implement the idea using checkboxes. Don't worry! There is a way to have each checkbox appear separately in the intended tab. Please check the following: www.exceldemy.com/wp-content/uploads/2024/05/Create-Tabs-Within-Tabs-by-adding-checkboxes-instead-of-typical-shapes.gif You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/05/Ayman-Farag-SOLVED.xlsm Excel VBA Code: Sub TabEpl() With Sheet1 .Range("B:H").EntireColumn.Hidden = False .Range("I:U").EntireColumn.Hidden = True End With End Sub Sub TabBundesliga() With Sheet1 .Range("I:O").EntireColumn.Hidden = False .Range("B:H,P:U").EntireColumn.Hidden = True End With End Sub Sub TabSeieA() With Sheet1 .Range("P:V").EntireColumn.Hidden = False .Range("B:O").EntireColumn.Hidden = True End With End Sub Private Sub CheckBox1_Change() If Sheet1.CheckBox1.Value = True Then Sheet1.CheckBox2.Value = False Sheet1.CheckBox3.Value = False Sheet1.CheckBox2.Visible = False Sheet1.CheckBox3.Visible = False
End Sub Private Sub CheckBox3_Change() If Sheet1.CheckBox3.Value = True Then Sheet1.CheckBox1.Value = False Sheet1.CheckBox2.Value = False Sheet1.CheckBox1.Visible = False Sheet1.CheckBox2.Visible = False
Dear @kevin66rn, Thank you for reaching out, and we appreciate your feedback. You asked an interesting question and the Exceldemy Forum is here to provide the answers to your questions free of cost. All you need to do is sign up in the Exceldemy Forum and post your question with the necessary details, you may attach a sample Excel file if you wish. Our Excel experts will help you with your challenges and provide the solution you need. Exceldemy Forum: exceldemy.com/forum/ Follow this link and sign up for the Exceldemy Forum to get free solutions to your Excel problems. Make sure to stay connected with Exceldemy!❤🥳. Regards, Exceldemy
How do I get this to work if I have multiple tabs with this system? When I copy Tab and move to end, and press the buttons, the page only change on the first tab.
Hello @joppejoppsson9604, After copying the tab you must reassign the buttons with the corresponding VBA code otherwise it may store the first tab. To get the system to work across multiple tabs please follow the steps given below: 1. When you copy and move tabs, the button macros might still reference the original tab. 2. For each newly copied tab, manually reassign the buttons to point to the correct VBA code associated with that specific tab. 3. Ensure the VBA code references the new sheet names and ranges. This approach ensures that each tab functions independently. Regards ExcelDemy
Hello @joppejoppsson9604, You are most welcome. Yes, you will need a VBA code for each tab. The VBA code will be the same for all tabs except the ranges. The cell range will vary based on the selection of corresponding tabs. Here is our VBA code for three-tab: Sub TabEpl() With Sheet1 .Shapes("EplOn").Visible = msoCTrue .Shapes("EplOff").Visible = msoFalse .Shapes("BundOn").Visible = msoFalse .Shapes("BundOff").Visible = msoCTrue .Shapes("SerieOn").Visible = msoFalse .Shapes("SerieOff").Visible = msoCTrue .Range("B:H").EntireColumn.Hidden = False .Range("I:U").EntireColumn.Hidden = True End With End Sub Sub TabBundesliga() With Sheet1 .Shapes("EplOn").Visible = msoFalse .Shapes("EplOff").Visible = msoCTrue .Shapes("BundOn").Visible = msoCTrue .Shapes("BundOff").Visible = msoFalse .Shapes("SerieOn").Visible = msoFalse .Shapes("SerieOff").Visible = msoCTrue .Range("I:O").EntireColumn.Hidden = False .Range("B:H,P:U").EntireColumn.Hidden = True End With End Sub Sub TabSeieA() With Sheet1 .Shapes("EplOn").Visible = msoFalse .Shapes("EplOff").Visible = msoCTrue .Shapes("BundOn").Visible = msoFalse .Shapes("BundOff").Visible = msoCTrue .Shapes("SerieOn").Visible = msoCTrue .Shapes("SerieOff").Visible = msoFalse .Range("P:V").EntireColumn.Hidden = False .Range("B:O").EntireColumn.Hidden = True End With End Sub Regards ExcelDemy
Hi there, great video and thanks for sharing. Is there any way to duplicate the tab in the same workbook and have the new tab contain all of the same macros? Thanks in advance!
Dear, Thanks for your kind words. Your appreciation means a lot to us. You want to duplicate the tabs in the same workbook but use the same macros. To do this, you have to use the ActiveSheet keyword instead of a particular sheet name. Also, you have to keep all the sub-procedures in a module. Please check the following: www.exceldemy.com/wp-content/uploads/2024/04/Duplicating-the-tabs-in-the-same-workbook-and-using-the-same-macros.gif You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/04/Rory-Bryant-SOLVED.xlsm Improved Excel VBA Sub-procedures: Sub TabEpl() On Error Resume Next With ActiveSheet .Shapes("EplOn").Visible = msoCTrue .Shapes("EplOff").Visible = msoFalse
.Range("B:H").EntireColumn.Hidden = False .Range("I:U").EntireColumn.Hidden = True End With End Sub Sub TabBundesliga() On Error Resume Next With ActiveSheet .Shapes("EplOn").Visible = msoFalse .Shapes("EplOff").Visible = msoCTrue
.Range("I:O").EntireColumn.Hidden = False .Range("B:H,P:U").EntireColumn.Hidden = True End With End Sub Sub TabSeieA() On Error Resume Next With ActiveSheet .Shapes("EplOn").Visible = msoFalse .Shapes("EplOff").Visible = msoCTrue
Dear, you need some AppleScript functions to develop such tabs within a tab in Google Sheets. The rest of the procedures will be almost like those in Excel. Of course! We will try as requested.
Hello @nancyg8506, Google Sheets does not support creating tabs within tabs like Excel does. However, hyperlinks to different sheets or sections within the same Google Sheet can achieve a similar effect. Follow the steps given below: Create tabs using buttons or text in Google Sheets: 1. Select cells in your sheet that will serve as your tabs, such as A1, B1, etc. 2. Format these cells with borders, background colors, and centered text to make them look like buttons or tabs. 3. Name each tab according to the sections of your sheet that they will link to, e.g., "Overview," "Data," or "Analysis." Now, link your tabs to different sheets or ranges in Google Sheets: 1. Select the cell you want to turn into a "tab." 2. Right-click and choose Insert link. 3. In the link box, you can choose Sheets in this spreadsheet and link to another sheet, or select Range and input a cell range within the current sheet. Clicking on the "tab" should now take you directly to the linked sheet or range. Finally, use Google Apps Script to create interactive features like hiding/showing content based on tab selection in Google Sheets. 1. Go to Extensions > Apps Script from the Google Sheets menu. 2. Use this sample code to hide/show specific rows or columns: function showTab1() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); sheet.hideRows(5, 10); // Example: hides rows 5 to 14 sheet.showRows(1, 4); // Shows rows 1 to 4 } function showTab2() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); sheet.hideRows(1, 4); // Hides rows 1 to 4 sheet.showRows(5, 10); // Shows rows 5 to 14 } Replace row/column ranges according to your sheet's structure. 3. Assign Script to Buttons Create buttons using Insert > Drawing and link each button to a corresponding script. This will allow users to click a button and reveal different parts of the sheet. Hopefully, it will give you a similar effect to Excel tabs within tabs. Regards ExcelDemy
Dear, thanks for the comment. If you want to create 7 tabs, there is nothing to worry about. You just need to make several more button shapes and sub-procedures. The rest of the ideas will be the same. You can share your problem within the ExcelDemy Forum if you have found difficulties when creating these tabs. ExcelDemy Forum: exceldemy.com/forum/
That was very creative! Thank you for sharing this video. I would’ve put this into a pivot chart and used the three tables as a filter at the top so I could just chose what table I wanted to see. There are other ways to set this up too but this way allows for a lot of options!
creative. well explained. i am thankful. please keep uploading such creative excel reporting/presentation videos.
Creative but I think that using Power Query and slicers would be much easier. This solution also requires no code at all.
Hello, can i please explain how to do it?
Excelente su creatividad y lo fácil que lo haces ver, de verdad impresionado. Gracias. Me agradaría hacer algo así con mi trabajo...
Dear, Thanks for your nice words! Your appreciation means a lot to us.
Thanks for the good work. Keep uploading creative content. I am very happy to see useful content.😀😀😀🥳🥳🎉🎉
The way you are explaining/teaching is very nice. there are little bit difference between vba code showing in video and in the attached file. However its very nice vide.
Thank you for a Very well-done and pleasant to hear/follow video. Appreciate the steps, I wish you the best of luck.
Hi, thank you so much for sharing this amazing concept. Pardon me, but when I attempted to add checkboxes to the sheet, they kept appearing in each tab. Is there a way to have each checkbox appear in a tab separately? Thanks in advance.
Dear, Thanks for thanking us! You are very welcome. We are glad that you have found the concept excellent. You tried adding checkboxes instead of typical shapes. However, you found it challenging to implement the idea using checkboxes.
Don't worry! There is a way to have each checkbox appear separately in the intended tab. Please check the following: www.exceldemy.com/wp-content/uploads/2024/05/Create-Tabs-Within-Tabs-by-adding-checkboxes-instead-of-typical-shapes.gif
You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/05/Ayman-Farag-SOLVED.xlsm
Excel VBA Code:
Sub TabEpl()
With Sheet1
.Range("B:H").EntireColumn.Hidden = False
.Range("I:U").EntireColumn.Hidden = True
End With
End Sub
Sub TabBundesliga()
With Sheet1
.Range("I:O").EntireColumn.Hidden = False
.Range("B:H,P:U").EntireColumn.Hidden = True
End With
End Sub
Sub TabSeieA()
With Sheet1
.Range("P:V").EntireColumn.Hidden = False
.Range("B:O").EntireColumn.Hidden = True
End With
End Sub
Private Sub CheckBox1_Change()
If Sheet1.CheckBox1.Value = True Then
Sheet1.CheckBox2.Value = False
Sheet1.CheckBox3.Value = False
Sheet1.CheckBox2.Visible = False
Sheet1.CheckBox3.Visible = False
Sheet1.CheckBox1.Left = Range("C4").Left
Sheet1.CheckBox1.Top = Range("C4").Top
Call TabEpl
Else
Sheet1.CheckBox2.Visible = True
Sheet1.CheckBox3.Visible = True
Sheet1.CheckBox1.Left = Range("B4").Left
Sheet1.CheckBox1.Top = Range("B4").Top
Sheet1.CheckBox2.Left = Range("D4").Left
Sheet1.CheckBox2.Top = Range("D4").Top
Sheet1.CheckBox3.Left = Range("F4").Left
Sheet1.CheckBox3.Top = Range("F4").Top
End If
End Sub
Private Sub CheckBox2_Change()
If Sheet1.CheckBox2.Value = True Then
Sheet1.CheckBox1.Value = False
Sheet1.CheckBox3.Value = False
Sheet1.CheckBox1.Visible = False
Sheet1.CheckBox3.Visible = False
Sheet1.CheckBox2.Left = Range("J4").Left
Sheet1.CheckBox2.Top = Range("J4").Top
Call TabBundesliga
Else
Sheet1.CheckBox1.Visible = True
Sheet1.CheckBox3.Visible = True
Sheet1.CheckBox1.Left = Range("I4").Left
Sheet1.CheckBox1.Top = Range("I4").Top
Sheet1.CheckBox2.Left = Range("K4").Left
Sheet1.CheckBox2.Top = Range("K4").Top
Sheet1.CheckBox3.Left = Range("M4").Left
Sheet1.CheckBox3.Top = Range("M4").Top
End If
End Sub
Private Sub CheckBox3_Change()
If Sheet1.CheckBox3.Value = True Then
Sheet1.CheckBox1.Value = False
Sheet1.CheckBox2.Value = False
Sheet1.CheckBox1.Visible = False
Sheet1.CheckBox2.Visible = False
Sheet1.CheckBox3.Left = Range("R4").Left
Sheet1.CheckBox3.Top = Range("R4").Top
Call TabSeieA
Else
Sheet1.CheckBox1.Visible = True
Sheet1.CheckBox2.Visible = True
Sheet1.CheckBox1.Left = Range("P4").Left
Sheet1.CheckBox1.Top = Range("P4").Top
Sheet1.CheckBox2.Left = Range("R4").Left
Sheet1.CheckBox2.Top = Range("R4").Top
Sheet1.CheckBox3.Left = Range("T4").Left
Sheet1.CheckBox3.Top = Range("T4").Top
End If
End Sub
Well done & thank you.
Hello @anonymoususer4356,
Thanks for your appreciation. You are most welcome. Please stay connected with us.
Regards
ExcelDemy
Awesome 👏
good job, nice one.
This tutorial is awesome. If I wanted to add a second tab in excel, how would the coding change?
Dear @kevin66rn,
Thank you for reaching out, and we appreciate your feedback. You asked an interesting question and the Exceldemy Forum is here to provide the answers to your questions free of cost.
All you need to do is sign up in the Exceldemy Forum and post your question with the necessary details, you may attach a sample Excel file if you wish. Our Excel experts will help you with your challenges and provide the solution you need.
Exceldemy Forum: exceldemy.com/forum/
Follow this link and sign up for the Exceldemy Forum to get free solutions to your Excel problems.
Make sure to stay connected with Exceldemy!❤🥳.
Regards,
Exceldemy
How do I get this to work if I have multiple tabs with this system? When I copy Tab and move to end, and press the buttons, the page only change on the first tab.
Hello @joppejoppsson9604,
After copying the tab you must reassign the buttons with the corresponding VBA code otherwise it may store the first tab.
To get the system to work across multiple tabs please follow the steps given below:
1. When you copy and move tabs, the button macros might still reference the original tab.
2. For each newly copied tab, manually reassign the buttons to point to the correct VBA code associated with that specific tab.
3. Ensure the VBA code references the new sheet names and ranges.
This approach ensures that each tab functions independently.
Regards
ExcelDemy
Ok. Thanks. So I should white/copy a new vba text för each tab?
Hello @joppejoppsson9604,
You are most welcome. Yes, you will need a VBA code for each tab. The VBA code will be the same for all tabs except the ranges. The cell range will vary based on the selection of corresponding tabs.
Here is our VBA code for three-tab:
Sub TabEpl()
With Sheet1
.Shapes("EplOn").Visible = msoCTrue
.Shapes("EplOff").Visible = msoFalse
.Shapes("BundOn").Visible = msoFalse
.Shapes("BundOff").Visible = msoCTrue
.Shapes("SerieOn").Visible = msoFalse
.Shapes("SerieOff").Visible = msoCTrue
.Range("B:H").EntireColumn.Hidden = False
.Range("I:U").EntireColumn.Hidden = True
End With
End Sub
Sub TabBundesliga()
With Sheet1
.Shapes("EplOn").Visible = msoFalse
.Shapes("EplOff").Visible = msoCTrue
.Shapes("BundOn").Visible = msoCTrue
.Shapes("BundOff").Visible = msoFalse
.Shapes("SerieOn").Visible = msoFalse
.Shapes("SerieOff").Visible = msoCTrue
.Range("I:O").EntireColumn.Hidden = False
.Range("B:H,P:U").EntireColumn.Hidden = True
End With
End Sub
Sub TabSeieA()
With Sheet1
.Shapes("EplOn").Visible = msoFalse
.Shapes("EplOff").Visible = msoCTrue
.Shapes("BundOn").Visible = msoFalse
.Shapes("BundOff").Visible = msoCTrue
.Shapes("SerieOn").Visible = msoCTrue
.Shapes("SerieOff").Visible = msoFalse
.Range("P:V").EntireColumn.Hidden = False
.Range("B:O").EntireColumn.Hidden = True
End With
End Sub
Regards
ExcelDemy
Cool 🎉
Wow
Hi there, great video and thanks for sharing. Is there any way to duplicate the tab in the same workbook and have the new tab contain all of the same macros? Thanks in advance!
Dear, Thanks for your kind words. Your appreciation means a lot to us. You want to duplicate the tabs in the same workbook but use the same macros. To do this, you have to use the ActiveSheet keyword instead of a particular sheet name. Also, you have to keep all the sub-procedures in a module.
Please check the following: www.exceldemy.com/wp-content/uploads/2024/04/Duplicating-the-tabs-in-the-same-workbook-and-using-the-same-macros.gif
You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/04/Rory-Bryant-SOLVED.xlsm
Improved Excel VBA Sub-procedures:
Sub TabEpl()
On Error Resume Next
With ActiveSheet
.Shapes("EplOn").Visible = msoCTrue
.Shapes("EplOff").Visible = msoFalse
.Shapes("BundOn").Visible = msoFalse
.Shapes("BundOff").Visible = msoCTrue
.Shapes("SerieOn").Visible = msoFalse
.Shapes("SerieOff").Visible = msoCTrue
.Range("B:H").EntireColumn.Hidden = False
.Range("I:U").EntireColumn.Hidden = True
End With
End Sub
Sub TabBundesliga()
On Error Resume Next
With ActiveSheet
.Shapes("EplOn").Visible = msoFalse
.Shapes("EplOff").Visible = msoCTrue
.Shapes("BundOn").Visible = msoCTrue
.Shapes("BundOff").Visible = msoFalse
.Shapes("SerieOn").Visible = msoFalse
.Shapes("SerieOff").Visible = msoCTrue
.Range("I:O").EntireColumn.Hidden = False
.Range("B:H,P:U").EntireColumn.Hidden = True
End With
End Sub
Sub TabSeieA()
On Error Resume Next
With ActiveSheet
.Shapes("EplOn").Visible = msoFalse
.Shapes("EplOff").Visible = msoCTrue
.Shapes("BundOn").Visible = msoFalse
.Shapes("BundOff").Visible = msoCTrue
.Shapes("SerieOn").Visible = msoCTrue
.Shapes("SerieOff").Visible = msoFalse
.Range("P:V").EntireColumn.Hidden = False
.Range("B:O").EntireColumn.Hidden = True
End With
End Sub
how can i do it for google excel..pls can you make video on it !!
Dear, you need some AppleScript functions to develop such tabs within a tab in Google Sheets. The rest of the procedures will be almost like those in Excel.
Of course! We will try as requested.
@@exceldemy2006 Thanks a lot !!
Dear,
You are most welcome. Please stay connected with us.
Regards
ExcelDemy
Can this be done in google sheets?
Hello @nancyg8506,
Google Sheets does not support creating tabs within tabs like Excel does. However, hyperlinks to different sheets or sections within the same Google Sheet can achieve a similar effect.
Follow the steps given below:
Create tabs using buttons or text in Google Sheets:
1. Select cells in your sheet that will serve as your tabs, such as A1, B1, etc.
2. Format these cells with borders, background colors, and centered text to make them look like buttons or tabs.
3. Name each tab according to the sections of your sheet that they will link to, e.g., "Overview," "Data," or "Analysis."
Now, link your tabs to different sheets or ranges in Google Sheets:
1. Select the cell you want to turn into a "tab."
2. Right-click and choose Insert link.
3. In the link box, you can choose Sheets in this spreadsheet and link to another sheet, or select Range and input a cell range within the current sheet.
Clicking on the "tab" should now take you directly to the linked sheet or range.
Finally, use Google Apps Script to create interactive features like hiding/showing content based on tab selection in Google Sheets.
1. Go to Extensions > Apps Script from the Google Sheets menu.
2. Use this sample code to hide/show specific rows or columns:
function showTab1() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
sheet.hideRows(5, 10); // Example: hides rows 5 to 14
sheet.showRows(1, 4); // Shows rows 1 to 4
}
function showTab2() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
sheet.hideRows(1, 4); // Hides rows 1 to 4
sheet.showRows(5, 10); // Shows rows 5 to 14
}
Replace row/column ranges according to your sheet's structure.
3. Assign Script to Buttons
Create buttons using Insert > Drawing and link each button to a corresponding script.
This will allow users to click a button and reveal different parts of the sheet.
Hopefully, it will give you a similar effect to Excel tabs within tabs.
Regards
ExcelDemy
I would like to create 7 tabs. How much more complicated would this be
Dear, thanks for the comment. If you want to create 7 tabs, there is nothing to worry about. You just need to make several more button shapes and sub-procedures. The rest of the ideas will be the same.
You can share your problem within the ExcelDemy Forum if you have found difficulties when creating these tabs.
ExcelDemy Forum: exceldemy.com/forum/