メインコンテンツへスキップ

別のシートのセル値に基づいて特定のワークシートを非表示または再表示するにはどうすればよいですか?

別のシートのセルの内容に基づいて特定のワークシートタブを非表示または再表示する方法はありますか? たとえば、Sheet1のセルG2に「はい」というテキストを入力すると、Sheet1を非表示にし、「いいえ」と入力すると、Sheet1が一度に表示されます。 Excelでこの問題を解決するにはどうすればよいですか?

VBAコードを使用してセル値に基づいて特定のワークシートタブを非表示または再表示します


矢印青い右バブル VBAコードを使用してセル値に基づいて特定のワークシートタブを非表示または再表示します

別のワークシートのセル値に基づいて特定のワークシートタブを非表示または再表示するには、次のVBAコードを使用するとよい場合があります。次のようにしてください。

1。 別のシートを非表示にするセル値を含むワークシートに移動します。

2。 シートタブを右クリックして、 コードを表示、飛び出した アプリケーション向け Microsoft Visual Basic ウィンドウの場合は、次のコードをコピーして空白のモジュールウィンドウに貼り付けてください。スクリーンショットを参照してください。

VBAコード:セル値に基づいてワークシートタブを非表示または非表示にします。

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

セル値1に基づくドキュメント非表示タブ

注: 上記のコードでは、 G1 はいは、ベースにしたいセルとセルのコンテンツです。 Sheet1 非表示または再表示する特定のシートです。 必要に応じて変更できます。

3。 次に、このコードを保存して閉じます。セルG1に「いいえ」またはその他のテキストを入力すると、Sheet1は非表示になりますが、セルに「はい」と入力すると、Sheet1がすぐに表示されます。スクリーンショットを参照してください。

セル値2に基づくドキュメント非表示タブ
1
セル値3に基づくドキュメント非表示タブ

 

最高のオフィス生産性向上ツール

人気の機能: 重複を検索、強調表示、または識別する   |  空白行を削除する   |  データを失わずに列またはセルを結合する   |   数式なしのラウンド ...
スーパールックアップ: 複数の基準の VLookup    複数の値の VLookup  |   複数のシートにわたる VLookup   |   ファジールックアップ ....
詳細ドロップダウン リスト: ドロップダウンリストを素早く作成する   |  依存関係のドロップダウン リスト   |  複数選択のドロップダウンリスト ....
列マネージャー: 特定の数の列を追加する  |  列の移動  |  Toggle 非表示列の表示ステータス  |  範囲と列の比較 ...
注目の機能: グリッドフォーカス   |  デザインビュー   |   ビッグフォーミュラバー    ワークブックとシートマネージャー   |  リソースライブラリ (自動テキスト)   |  日付ピッカー   |  ワークシートを組み合わせる   |  セルの暗号化/復号化    リストごとにメールを送信する   |  スーパーフィルター   |   特殊フィルター (太字/斜体/取り消し線をフィルター...) ...
上位 15 のツールセット12 テキスト 工具 (テキストを追加, 文字を削除する、...)   |   50+ チャート 種類 (ガントチャート、...)   |   40+ 実用的 (誕生日に基づいて年齢を計算する、...)   |   19 挿入 工具 (QRコードを挿入, パスから画像を挿入、...)   |   12 変換 工具 (数字から言葉へ, 通貨の換算、...)   |   7 マージ&スプリット 工具 (高度な結合行, 分割セル、...)   |   ... もっと

Kutools for Excel で Excel スキルを強化し、これまでにない効率を体験してください。 Kutools for Excelは、生産性を向上させ、時間を節約するための300以上の高度な機能を提供します。  最も必要な機能を入手するにはここをクリックしてください...

kteタブ201905


Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作​​業をはるかに簡単にします

  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!

 

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