Passer au contenu principal

Comment masquer ou afficher une feuille de calcul spécifique en fonction de la valeur de la cellule dans une autre feuille?

Existe-t-il des méthodes pour masquer ou afficher un onglet de feuille de calcul spécifique en fonction du contenu d'une cellule dans une autre feuille? Par exemple, lorsque je saisis le texte «Oui» dans la cellule G1 de la feuille Sheet2, je veux que la feuille Sheet1 soit masquée, et lorsque je saisis «Non», la feuille Sheet1 s'affiche immédiatement. Comment pourrais-je résoudre ce problème dans Excel?

Masquer ou afficher un onglet de feuille de calcul spécifique en fonction de la valeur de la cellule avec le code VBA

flèche bleue bulle droite Masquer ou afficher un onglet de feuille de calcul spécifique en fonction de la valeur de la cellule avec le code VBA

Pour masquer ou afficher un onglet de feuille de calcul spécifique en fonction d'une valeur de cellule dans une autre feuille de calcul, le code VBA suivant peut vous rendre service, procédez comme suit:

1. Accédez à la feuille de calcul qui contient la valeur de cellule sur laquelle vous souhaitez masquer une autre feuille.

2. Cliquez avec le bouton droit sur l'onglet de la feuille et sélectionnez Voir le code, dans le sauté Microsoft Visual Basic pour applications fenêtre, veuillez copier et coller le code suivant dans la fenêtre Module vide, voir capture d'écran:

Code VBA: masquez ou masquez un onglet de feuille de calcul en fonction de la valeur de la cellule:

Private Sub Worksheet_Change(ByVal Target As Range)
If [G1] = "Yes" Then
Sheets("Sheet1").Visible = True
Else
Sheets("Sheet1").Visible = False
End If
End Sub

doc hide tab en fonction de la valeur de cellule 1

Remarque: Dans le code ci-dessus, G1 et Oui sont la cellule et le contenu de cellule sur lesquels vous souhaitez vous baser, et Sheet1 est la feuille spécifique que vous souhaitez masquer ou afficher. Vous pouvez les modifier selon vos besoins.

3. Ensuite, enregistrez et fermez ce code, lorsque vous entrez «Non» ou un autre texte dans la cellule G1, Sheet1 est masqué, mais si vous entrez «Oui» dans la cellule, Sheet1 s'affiche à la fois, voir les captures d'écran:

doc hide tab en fonction de la valeur de cellule 2
1
doc hide tab en fonction de la valeur de cellule 3

 

Meilleurs outils de productivité bureautique

Caractéristiques populaires: Rechercher, mettre en évidence ou identifier les doublons   |  Supprimer les lignes vides   |  Combinez des colonnes ou des cellules sans perdre de données   |   Tour sans formule 
Super recherche: VSearchup à critères multiples  |   VSearch à valeurs multiples  |   Recherche virtuelle sur plusieurs feuilles   |   Recherche floue ....
Liste déroulante avancée: Créez rapidement une liste déroulante   |  Liste déroulante dépendante   |  Liste déroulante à sélection multiple ....
Gestionnaire de colonnes: Ajouter un nombre spécifique de colonnes  |  Déplacer les colonnes  |  Basculer l'état de visibilité des colonnes masquées  |  Comparer les plages et les colonnes 
Caractéristiques en vedette: Mise au point de la grille   |  Voir Design   |   Grande barre de formule   |  Gestionnaire de classeurs et de feuilles   |  Centre de ressources (Texte automatique)   |  Sélecteur de date   |  Combiner des feuilles de travail   |  Crypter/déchiffrer les cellules   |  Envoyer des e-mails par liste   |  Super filtre   |   Filtre spécial (filtre gras/italique/barré...) ...
Les 15 meilleurs ensembles d'outils:  12 Texte Outils (Ajouter du texte, Supprimer les caractères, ...)   |   50+ Graphique Types (Diagramme de Gantt, ...)   |   40+ Pratique Formules (Calculer l'âge en fonction de l'anniversaire, ...)   |   19 Insertion Outils (Insérer le code QR, Insérer une image à partir du chemin, ...)   |   12 Conversion Outils (Nombres en mots, Conversion des devises, ...)   |   7 Fusionner et fractionner Outils (Lignes de combinaison avancées, Cellules divisés, ...)   |   ... et plus

Boostez vos compétences Excel avec Kutools for Excelet faites l'expérience d'une efficacité comme jamais auparavant. Kutools for Excel Offre plus de 300 fonctionnalités avancées pour augmenter la productivité et gagner du temps.  Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...

onglet kte 201905

Office Tab Apporte une interface à onglets à Office et facilite grandement votre travail

  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!

 

Comments (24)
No ratings yet. Be the first to rate!
Bob
Bob
This comment was minimized by the moderator on the site
I am trying to use this but it is coming up with "compile error - can't find project or library" and highlighting the cell where the Yes/No dropdown is situated. I think it may be due to this being a merged cell, is there any way around this?
Reply
0
0
Margaret M.
Margaret M.
This comment was minimized by the moderator on the site
I'm having an issue where I want the tab to show for a range of values, rather than just a YES or NO. When I try to repeat the IF statement in the sub, it gives me an error and when I try to list multiple values in the if statement, I get an error. Any ideas?

It almost seems like I need to use an IF/OR statement but not sure how that would work.

Private Sub Worksheet_Change(ByVal Target As Range)
If [K6] = "VS 1", "VS 2", "VS 3", VS 4" Then
Sheets("Page6").Visible = True
Else
Sheets("Page6").Visible = False
End If
End Sub
Reply
0
0
skyyang
skyyang     Margaret M.
This comment was minimized by the moderator on the site
Hello, Margaret,
To solve your problem, please apply the below code, you should use Or to join the conditions.
Private Sub Worksheet_Change(ByVal Target As Range)
If [K6] = "VS 1" Or [K6] = "VS 2" Or [K6] = "VS 3" Or [K6] = "VS 4" Then
Sheets("Page6").Visible = True
Else
Sheets("Page6").Visible = False
End If
End Sub

Please have a try, hope it can help you!
Reply
Report
0
0
Margaret M.
Margaret M.     skyyang
This comment was minimized by the moderator on the site
Skyyang - this worked perfectly. Thank you for your prompt response!
Reply
0
0
Andy Lannon
Andy Lannon
This comment was minimized by the moderator on the site
I have a value in Cell B1 that if yes unhides sheet1 but also a value in B2 that's if yes unhides Sheet2 etc etc.. it hides/unhides the last sheet fine but not the rest, how would i go about having say 10 values unhiding 10 different sheets? thanks
Reply
0
0
skyyang
skyyang     Andy Lannon
This comment was minimized by the moderator on the site
Hello, Andy,
To solve your problem, please apply the below code:
Private Sub Worksheet_Change(ByVal Target As Range)
If [B2] = "Yes" Then
Sheets("Sheet1").Visible = True
Else
Sheets("Sheet1").Visible = False
End If
If [B3] = "Yes" Then
Sheets("Sheet2").Visible = True
Else
Sheets("Sheet2").Visible = False
End If
If [B4] = "Yes" Then
Sheets("Sheet3").Visible = True
Else
Sheets("Sheet3").Visible = False
End If
If [B5] = "Yes" Then
Sheets("Sheet4").Visible = True
Else
Sheets("Sheet4").Visible = False
End If
If [B6] = "Yes" Then
Sheets("Sheet5").Visible = True
Else
Sheets("Sheet5").Visible = False
End If
End Sub

Note: In the above code, you just need to copy the below scripts several times and change the cell reference and sheet name to your own.
If [B2] = "Yes" Then
Sheets("Sheet1").Visible = True
Else
Sheets("Sheet1").Visible = False
End If


Please have a try, hope it can help you!
Reply
Report
0
0
Liz
Liz
This comment was minimized by the moderator on the site
This code works great, However I have 42 variables for 70 sheets so this code gets very long and stops working. Is there a way to make this code in sections as to not exceed the code size that VBA can handle? Or do I have to divide these into two separate excels?
Reply
0
0
skyyang
skyyang     Liz
This comment was minimized by the moderator on the site
Hello, Liz
Sorry, at present, there is not a good way for resolving your problem.
Reply
Report
0
0
Влад
Влад
This comment was minimized by the moderator on the site
А я вот не могу понять, как такое сделать в гугл таблицах именно?
Reply
0
0
MandyZhou
MandyZhou     Влад
This comment was minimized by the moderator on the site
Hello friend,

You can open a new Google sheet, add a new sheet so that there is a "Sheet1" and a "Sheet2" then go to Tools > Script Editor. In the editor, paste the following.

function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet1 = ss.getSheetByName("Sheet1");
var sheet2 = ss.getSheetByName("Sheet2");

var cell1 = sheet1.getRange('B2');

if (cell1.getValue() == 2) {
sheet2.hideSheet();
}

if (cell1.getValue() == 3) {
sheet2.showSheet();
}
}

Please have a try.

Sincerely,
Mandy
Reply
Report
0
0
Helen Hay
Helen Hay
This comment was minimized by the moderator on the site
I want to have a Menu/Table of Contents as my first worksheet and based on Yes/No answers - certain worksheets appear i.e. more than 1 becomes visible.
I'm struggling to have several worksheets appear based on the above code.
Can I use an AND function e.g. If X cell = "Yes" Then Make Y Sheet visible and Z Sheet and K Sheet?
Any advice greatly welcomed.
Regards
Helen
Reply
0
0
Jean
Jean
This comment was minimized by the moderator on the site
I am looking to hide or unhide sheets based on a cell value (Yes/No). The values are in a table (tblFileContents) and column D4:D25 and the sheet name is in A4:A25 on sheet <File Content>. The order of the items can be changed. Do you have a reference to an example that would allow me to do this?
Reply
0
0
pY
pY     Jean
This comment was minimized by the moderator on the site
I have the same issue and someone else provided a solution.
Make a table (format as table) with 2 columns, name the left column Sheets to show/hide, the right column is up  to you.Put all sheet names in left column
Then select View Code for this tab and enter Dim Changed As Range, c As Range, rMTS As Range
Dim i As Long
Dim bShowAll As Boolean

Set rMTS = Range("TblShowHide[Mark to Show]")
Set Changed = Intersect(Target, rMTS)
If Not Changed Is Nothing Then
bShowAll = Len(rMTS.Cells(1).Value) > 0
On Error Resume Next
For i = 2 To rMTS.Rows.Count
Sheets(Range("TblShowHide[Show/Hide Sheets]").Cells(i).Value).Visible = IIf(bShowAll, True, Len(rMTS.Cells(i).Value) > 0)
Next i
On Error GoTo 0
End If
End Sub


Reply
0
0
Jean
Jean     pY
This comment was minimized by the moderator on the site
I will give this a try. Thank you very much!
Reply
0
0
Jean
Jean     Jean
This comment was minimized by the moderator on the site
Not sure what I need to change but it didn't work for me.
Reply
0
0
pyclen
pyclen     Jean
This comment was minimized by the moderator on the site
Hi Jean,
yes I have an example I attached here. Rename the file to example.xlsm (it is not zipped, but had to rename to upload)
On the menu tab there is a table with the various tab names, make an x or any other character to show the tab, if you remove the character, the tab hides.
Hope that helps
View Attachments (1 / 5)
Reply
Report
0
0
Kruzer
Kruzer     pyclen
This comment was minimized by the moderator on the site
This contains several items but no excel file.
Report
0
0
JELEE
JELEE
This comment was minimized by the moderator on the site
HiI have a workbook with multiple named tabs linked to an index sheet at the front. The user can select the sheets they want to use by checking a box next to the sheet name - blank, N/A or Yes (dropdown list). Is there a way of adapting this code so that the sheet is visible if the check box is blank or contains "Yes" but not visible if the checkbox contains "N/A".
I have tried but do not know enough about VBA to make it work. Thanks
Reply
Report
0
0
Andrew
Andrew
This comment was minimized by the moderator on the site
I did 2 worksheets as follows:
Private Sub Worksheet_Change(ByVal Target As Range)
If [C20] = "Yes" Then
Sheets("sheet1").Visible = True
Else
Sheets("sheet1").Visible = False
End If
If [C22] = "Yes" Then
Sheets("sheet2").Visible = True
Else
Sheets("sheet2").Visible = False
End If
End Sub
Reply
0
0
malhotra.tarun85
malhotra.tarun85     Andrew
This comment was minimized by the moderator on the site
Hi Team,

I need similar code I have table range B10 : G40 where in in Column B11 I have sheets name and Column C11:G40 I have data validations as Yes /No. I need code to hide or visible sheets as per columns B if Its YES in Column C:G. Please help.

Sheets CIPS PE RE BANKS IM
IFRS 1 Yes Yes Yes Yes Yes
IFRS 2 Yes Yes Yes Yes Yes
IFRS 3 Yes Yes Yes Yes Yes
IFRS 5 Yes Yes Yes Yes Yes
IFRS 6 No No No No No
IFRS 7 Yes Yes No Yes Yes
IFRS 13 No Yes No Yes Yes
IFRS 14 No No No No No
IFRS 15 Yes No No No No
IFRS 16 Yes No Yes No No
IAS 1 Yes Yes Yes Yes Yes
IAS 2 No No Yes No No
IAS 7 Yes Yes Yes Yes Yes
IAS 8 Yes Yes Yes Yes Yes
IAS 10 Yes Yes Yes Yes Yes
IAS 12 No No No No No
IAS 16 No No No No No
IAS 19 No No No No No
IAS 20 Yes Yes Yes Yes Yes
IAS 21 Yes Yes Yes Yes Yes
IAS 23 Yes Yes Yes Yes Yes
IAS 24 Yes Yes Yes Yes Yes
IAS 27 Yes Yes Yes Yes Yes
IAS 29 Yes Yes Yes Yes Yes
IAS 32 No No No Yes Yes
IAS 34 Yes Yes Yes Yes Yes
IAS 36 Yes Yes Yes Yes No
IAS 38 Yes No No Yes No
IAS 40 Yes No Yes Yes No
IAS 41 No No No No No
Reply
Report
0
0
Cliffork
Cliffork     Andrew
This comment was minimized by the moderator on the site
I want to see if you can help with my issue. I've had essentially this exact code in a sheet I use but with an or function so the answer can be "yes" or "true". However, the sheet that is being hidden/unhidden sometimes re-hides itself for unknown reasons. It will unhide when I type yes, but when I go to use it later on it is hidden again, even though the cell value is still yes. Any idea why this might be happening, and/or how to fix it?
Code for reference:
Private Sub Worksheet_Change(ByVal Target As Range)'Hides/unhides Calibration page
If [B18] = "Yes" Or Target.Value = "True" Then
Sheets("XXX Verification").Visible = True
Else
Sheets("XXX Verification").Visible = False
End If

Reply
Report
0
0
Guest
Guest     Andrew
This comment was minimized by the moderator on the site
Very helpful!
Reply
0
0
walidorafrafi@gmail.com
This comment was minimized by the moderator on the site
Hello
Thank you for the tip. I need to do the same scenario but not on one cell only (G1 in this case) but on all cells of column G. I have tried with "Range" but it didn't work.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("X2:X100") = "" Then
Sheets("EU TASK BASED MEASUREMENTS").Visible = False
Else
Sheets("EU TASK BASED MEASUREMENTS").Visible = True
End If
End Sub


Thank you in advance
Reply
Report
0
0
Scott Hobin
Scott Hobin    
This comment was minimized by the moderator on the site
Any chance this was answered? I am also running into this scenario where a whole range comes into play instead of just one cell... I used this same coding with the same results.
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations