Excel file with many tabs
Solved
bleucalyptus
Posted messages
1
Status
Membre
-
Raymond PENTIER Posted messages 58551 Registration date Status Contributeur Last intervention -
Raymond PENTIER Posted messages 58551 Registration date Status Contributeur Last intervention -
Hello,
I have an Excel file with many tabs that are not sorted alphabetically. How can I quickly find the tab I'm looking for?
(Creating a sheet that references the tabs? Automatic linking?)
Thank you in advance!
I have an Excel file with many tabs that are not sorted alphabetically. How can I quickly find the tab I'm looking for?
(Creating a sheet that references the tabs? Automatic linking?)
Thank you in advance!
Configuration: Windows XP Internet Explorer 7.0
7 réponses
Hello,
First possibility:
In the workbook, right-click on the arrows located at the bottom left, a list of tabs will appear. If the list is very long, click on "more sheets..." in the list, a dialog box will appear with the list of tabs as well. You can either scroll to reach the name of the desired tab or type the first letters of the tab you want, then select it and click on "OK".
Second possibility:
A VBA macro, with the creation of a UserForm (with a ComboBox, a "Quit" button, and an "OK" button) containing the following code:
(workbook containing this macro: https://www.cjoint.com/?egqIy4MLTf)
and a module containing the following macro:
Regarding this macro, if it is placed in the personal macro workbook, it can be used on all active workbooks.
There you go, I hope this meets what you're looking for.
Have a nice end of the day
--
"To find a solution to one's problems, one must make an effort."
First possibility:
In the workbook, right-click on the arrows located at the bottom left, a list of tabs will appear. If the list is very long, click on "more sheets..." in the list, a dialog box will appear with the list of tabs as well. You can either scroll to reach the name of the desired tab or type the first letters of the tab you want, then select it and click on "OK".
Second possibility:
A VBA macro, with the creation of a UserForm (with a ComboBox, a "Quit" button, and an "OK" button) containing the following code:
(workbook containing this macro: https://www.cjoint.com/?egqIy4MLTf)
Private Sub cbListeOngletsClasseur_Change() cbListeOngletsClasseur.MatchRequired = True cbListeOngletsClasseur.MatchEntry = fmMatchEntryComplete End Sub Private Sub cbListeOngletsClasseur_Click() Select Case cbListeOngletsClasseur.ListIndex End Select End Sub Private Sub cmdOK_Click() Num = (cbListeOngletsClasseur.ListIndex) + 1 If Num = 0 Then MsgBox "You have not selected anything, PLEASE TRY AGAIN!!!" Unload fmListeOngletsClasseur Call ChoixOnglet Else Sheets.Item(Num).Select Unload fmListeOngletsClasseur End If End Sub Private Sub cmdOK_Enter() Num = (cbListeOngletsClasseur.ListIndex) + 1 If Num = 0 Then MsgBox "You have not selected anything, PLEASE TRY AGAIN!!!" Unload fmListeOngletsClasseur Call ChoixOnglet Else Sheets.Item(Num).Select Unload fmListeOngletsClasseur End If End Sub Private Sub cmdQuitter_Click() fmListeOngletsClasseur.Hide Unload fmListeOngletsClasseur End Sub Private Sub UserForm_Initialize() Dim Numfeuille As Integer Dim NomFeuille As Variant For Numfeuille = 1 To Worksheets.Count NomFeuille = Worksheets(Numfeuille).Name cbListeOngletsClasseur.AddItem "" & NomFeuille Next Numfeuille cbListeOngletsClasseur.Style = fmStyleDropDownCombo End Sub
and a module containing the following macro:
Sub ChoixOnglet() Load fmListeOngletsClasseur fmListeOngletsClasseur.Show End Sub
Regarding this macro, if it is placed in the personal macro workbook, it can be used on all active workbooks.
There you go, I hope this meets what you're looking for.
Have a nice end of the day
--
"To find a solution to one's problems, one must make an effort."
Hello,
I also encountered this issue with tab management. Lacking the skill to create macros or VBA scripts, I simply created an additional sheet named "Index" where I list all the workbook tabs in a column and link them to the target sheet via a hyperlink:
Manipulation:
- 1 In the created sheet, in the column containing the names of the tabs
- 2 Select the cell containing the name of the tab to link
- 3 Right-click - Context menu - Hyperlink
- 4 In the window that opens - left column - click on "Place in this document"
- 5 In the window that opens again listing the sheet tabs, select the corresponding tab.
- 6 Click and confirm the selection.
Of course, this is longer and not automatic, but there's an advantage to this drawback
The index sheet allows me to add information next to the list of tabs related to the target sheet, which is very interesting in my case; I can also organize and sort this list based on the filters I need.
Perhaps this somewhat caveman solution will be useful to you.
Good luck!
I also encountered this issue with tab management. Lacking the skill to create macros or VBA scripts, I simply created an additional sheet named "Index" where I list all the workbook tabs in a column and link them to the target sheet via a hyperlink:
Manipulation:
- 1 In the created sheet, in the column containing the names of the tabs
- 2 Select the cell containing the name of the tab to link
- 3 Right-click - Context menu - Hyperlink
- 4 In the window that opens - left column - click on "Place in this document"
- 5 In the window that opens again listing the sheet tabs, select the corresponding tab.
- 6 Click and confirm the selection.
Of course, this is longer and not automatic, but there's an advantage to this drawback
The index sheet allows me to add information next to the list of tabs related to the target sheet, which is very interesting in my case; I can also organize and sort this list based on the filters I need.
Perhaps this somewhat caveman solution will be useful to you.
Good luck!
Given the complexity of the different operations proposed, you could just as easily organize your tabs in alphabetical order by dragging them with your mouse.
--
A happy retired Antillean :-)
Raymond
--
A happy retired Antillean :-)
Raymond
Well no Raymond, hypertext links are not complex. They are easy and quick to implement.
You're right, that shouldn't stop Bleucalyptus from rearranging their tabs by dragging with the mouse. But then to quickly access each of the 65 tabs, links are quite handy.
Still disagree?
The Aquarelle macro works well too. But, well, I'm a bit upset with… (the macros of course)
Best regards.
--
Argitxu
You're right, that shouldn't stop Bleucalyptus from rearranging their tabs by dragging with the mouse. But then to quickly access each of the 65 tabs, links are quite handy.
Still disagree?
The Aquarelle macro works well too. But, well, I'm a bit upset with… (the macros of course)
Best regards.
--
Argitxu
Good evening Bleucalyptus,
There are also hyperlinks that allow you to go directly to the desired tab.
You should insert a tab at the beginning that you could name "summary."
The process is simple: In A1, right-click / Hyperlink / Place in this document.
There, it displays all the tabs in the file and you can link to each of the tabs on each of the cells from A1 to A65.
You can also dedicate a cell on each tab to create a link that brings you back to the summary.
http://www.cijoint.fr/cjlink.php?file=cj200804/cijA6F2sKH.xls
Have a good evening
--
Argitxu
There are also hyperlinks that allow you to go directly to the desired tab.
You should insert a tab at the beginning that you could name "summary."
The process is simple: In A1, right-click / Hyperlink / Place in this document.
There, it displays all the tabs in the file and you can link to each of the tabs on each of the cells from A1 to A65.
You can also dedicate a cell on each tab to create a link that brings you back to the summary.
http://www.cijoint.fr/cjlink.php?file=cj200804/cijA6F2sKH.xls
Have a good evening
--
Argitxu
Hello everyone,
In any case, I believe that Bleucalyptus has quite a wide range of more or less complex possibilities available to him and can therefore make his choice based on what he truly expects.
It's quite nice to discover everything that can be done on a question; it gives ideas to both the person asking and those offering.
As for simplicity, I think the first option I provided is indeed simple; he doesn't have to create or do anything other than a few clicks since it's included in the context menus of Excel ---> so it's very easy to apply for anyone.
Have a great day
PS: For argixu, I had a moment of fright ;-))
--
"To find a solution to one's problems, one must make an effort."
In any case, I believe that Bleucalyptus has quite a wide range of more or less complex possibilities available to him and can therefore make his choice based on what he truly expects.
It's quite nice to discover everything that can be done on a question; it gives ideas to both the person asking and those offering.
As for simplicity, I think the first option I provided is indeed simple; he doesn't have to create or do anything other than a few clicks since it's included in the context menus of Excel ---> so it's very easy to apply for anyone.
Have a great day
PS: For argixu, I had a moment of fright ;-))
--
"To find a solution to one's problems, one must make an effort."
Hello
I want to perform a task on Excel and after reading this post, I think I'm in the right place
Is it possible to display the tabs or different sheets of the workbook in 2 thicknesses so I can show more sheets?
Am I being clear enough? lol
Thank you in advance for your responses.
I want to perform a task on Excel and after reading this post, I think I'm in the right place
Is it possible to display the tabs or different sheets of the workbook in 2 thicknesses so I can show more sheets?
Am I being clear enough? lol
Thank you in advance for your responses.
<i>Is it possible to set the line or the different tabs of the workbook to appear in 2 thicknesses in order to display more sheets?</i> <br />NO. <br />The solution proposed by Microsoft is described to you by <b>aquarelle</b> in <b>post 1</b> of this discussion under "First possibility". <br />-- <br />Retirement is great! Especially in the Caribbean... <:-)> <br /> ☻ Raymond ♂