Hoppa till huvudinnehåll

Hur döljer eller visar jag ett visst kalkylblad baserat på cellvärde i ett annat ark?

Finns det några metoder för oss att dölja eller ta bort en specifik kalkylflik på basis av ett cellinnehåll i ett annat ark? När jag till exempel skriver in texten ”Ja” i cellen G1 i Sheet2 vill jag att Sheet1 ska döljas och när jag anger “No” ska Sheet1 visas på en gång. Hur kunde jag lösa detta problem i Excel?

Dölj eller ta bort en specifik kalkylflik på basis av cellvärde med VBA-kod


pil blå höger bubbla Dölj eller ta bort en specifik kalkylflik på basis av cellvärde med VBA-kod

För att dölja eller ta bort en viss kalkylflik på basis av ett cellvärde i ett annat kalkylblad kan följande VBA-kod göra dig en tjänst, gör så här:

1. Gå till kalkylbladet som innehåller cellvärdet du vill dölja ett annat ark baserat på.

2. Högerklicka på arkfliken och välj Visa kod, i poppade ut Microsoft Visual Basic för applikationer fönster, kopiera och klistra in följande kod i det tomma modulfönstret, se skärmdump:

VBA-kod: Dölj eller dölj en kalkylbladflik baserat på cellvärde:

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

fliken doc gömma baserat på cellvärde 1

Notera: I ovanstående kod, G1 och Ja är det cell- och cellinnehåll som du vill basera på, och Sheet1 är det specifika arket som du vill dölja eller visa. Du kan ändra dem efter dina behov.

3. Spara och stäng sedan den här koden, när du anger "Nej" eller annan text i cell G1, är Dölj 1 dolt, men om du anger "Ja" i cellen, visas Blad 1 på en gång, se skärmdumpar:

fliken doc gömma baserat på cellvärde 2
1
fliken doc gömma baserat på cellvärde 3

 

Bästa kontorsproduktivitetsverktyg

Populära funktioner: Hitta, markera eller identifiera dubbletter   |  Ta bort tomma rader   |  Kombinera kolumner eller celler utan att förlora data   |   Rund utan formel ...
Superuppslag: Flera kriterier VLookup    VLookup med flera värden  |   VSök över flera ark   |   Fuzzy Lookup ....
Avancerad rullgardinslista: Skapa snabbt en rullgardinslista   |  Beroende rullgardinslista   |  Flervals-rullgardinslista ....
Kolumnhanterare: Lägg till ett specifikt antal kolumner  |  Flytta kolumner  |  Växla synlighetsstatus för dolda kolumner  |  Jämför intervall och kolumner ...
Utvalda funktioner: Rutnätsfokus   |  Designvy   |   Stor formelbar    Arbetsbok & Bladhanterare   |  Resursbibliotek (Automatisk text)   |  Datumväljare   |  Kombinera arbetsblad   |  Kryptera/Dekryptera celler    Skicka e-postmeddelanden efter lista   |  Superfilter   |   Specialfilter (filtrera fet/kursiv/genomstruken...) ...
Topp 15 verktygssatser12 text verktyg (lägga till text, Ta bort tecken, ...)   |   50 + Diagram Typer (Gantt Chart, ...)   |   40+ Praktiskt Formler (Beräkna ålder baserat på födelsedag, ...)   |   19 Införande verktyg (Infoga QR-kod, Infoga bild från sökväg, ...)   |   12 Konvertering verktyg (Siffror till ord, Valutaväxling, ...)   |   7 Slå ihop och dela verktyg (Avancerade kombinera rader, Dela celler, ...)   |   ... och mer

Uppgradera dina Excel-färdigheter med Kutools för Excel och upplev effektivitet som aldrig förr. Kutools för Excel erbjuder över 300 avancerade funktioner för att öka produktiviteten och spara tid.  Klicka här för att få den funktion du behöver mest...

kte-flik 201905


Fliken Office ger ett flikgränssnitt till Office och gör ditt arbete mycket enklare

  • Aktivera flikredigering och läsning i Word, Excel, PowerPoint, Publisher, Access, Visio och Project.
  • Öppna och skapa flera dokument i nya flikar i samma fönster, snarare än i nya fönster.
  • Ökar din produktivitet med 50 % och minskar hundratals musklick för dig varje dag!

 

Comments (24)
No ratings yet. Be the first to rate!
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?
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
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!
This comment was minimized by the moderator on the site
Skyyang - this worked perfectly. Thank you for your prompt response!
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
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!
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?
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.
This comment was minimized by the moderator on the site
А я вот не могу понять, как такое сделать в гугл таблицах именно?
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
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
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?
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


This comment was minimized by the moderator on the site
I will give this a try. Thank you very much!
This comment was minimized by the moderator on the site
Not sure what I need to change but it didn't work for me.
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
This comment was minimized by the moderator on the site
This contains several items but no excel file.
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
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
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
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

This comment was minimized by the moderator on the site
Very helpful!
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
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.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations