根据单元格值隐藏或取消隐藏工作表 [英] Hide or unhide sheet depending on cell value

问题描述

我希望根据单元格的值编写有关在Excel中隐藏或取消隐藏工作表的代码.

I am looking to write a code about hiding or un-hiding worksheets in Excel depending the value of a cell.

我已经达到了

Sub Hide_Un()
    If Range("b4").Value = "yes" Then
        sheets(2).Visible = True
    ElseIf Range("b4").Value = "no" Then
        sheets(2).Visible = False
    End If

    If Range("b5").Value = "yes" Then
        sheets(3).Visible = True
    ElseIf Range("b5").Value = "no" Then
        sheets(3).Visible = False
    End If

大约有100张纸，每次添加新纸时我都无法执行此步骤.

There are about 100 sheets and I can't do this procedure every time I add a new sheet.

我需要一个代码来隐藏或取消隐藏每个工作表，这取决于我对单元格的声明.

I need a code to hide or unhide each worksheet depending my declaration to a cell.

示例B1 ="ys"(可见)或B1 ="no"(不可见)

Example B1="yes" (visible) or B1="no" (not visible)

推荐答案

1. Option Compare Text使其不区分大小写.这样YES = yes.没有此选项，它们将不相等
2. 您可能需要考虑该值的选项不是yes或no.如果值是y或n或yes并带有滞后空格怎么办?
3. 如果用户可以选择在书中添加/移动/删除工作表，则使用工作表索引号(Sheet(n))可能会出现问题.
4. 看来行号与Sheet # = Row -2中的图纸号有关.我从4开始循环.最低可能是3-否则，您最终将试图隐藏可能不存在的工作表

1. Option Compare Text makes this non case sensitive. This way YES = yes. Without this Option, they would not be equal
2. You may need to account for the option of the value being neither yes or no. What if value is y or n or yes with a lagging space?
3. Using the sheet index number (Sheet(n)) can be problematic if users have the option to add/move/delete sheets in the book.
4. It looks like the row number relates to sheet number by Sheet # = Row -2. I am starting the loop at 4. The lowest this could be is 3 - other wise you will end up trying to hide a sheet that cannot exist

---

Option Explicit
Option Compare Text

Sub Hide_Un()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("TOC")
Dim i

Application.ScreenUpdating = False
    For i = 4 To ws.Range("B" & ws.Rows.Count).End(xlUp).Row
        If ws.Range("B" & i) = "yes" Then
            ThisWorkbook.Sheets(i - 2).Visible = xlSheetVisible
        ElseIf ws.Range("B" & i) = "no" Then
            ThisWorkbook.Sheets(i - 2).Visible = xlSheetHidden
        Else 'What if the cell is neither?
            'Do what you want if the cell is not "yes" or "no"
        End If
    Next i
Application.ScreenUpdating = True

End Sub

这篇关于根据单元格值隐藏或取消隐藏工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！