VBA基于YES/NO和工作表名称隐藏和显示工作表

Question

我有一个有很多页的工作簿。我有一个索引工作表，列出了B列工作表的名称和c列是/否问题。我想要一个代码，当我点击"是"它将打开它所对应的工作表。当我点击"no"时;它将隐藏工作表。列表中的名称与工作表的名称完全匹配。我猜它会有一个循环?

范围从B27到C61

Answer 1

如果我们假设第2列包含表单的名称，第3列包含Yes或No，那么这段代码将使用双击:

Option Explicit
Private Function GetSheet(SheetName As String) As Worksheet
On Error Resume Next
Set GetSheet = ThisWorkbook.Sheets(SheetName)
If Err.Number <> 0 Then
Err.Clear
End If
End Function
Private Sub ShowHideClick(Rng As Range, ActivateSheet As Boolean)
Dim RowNum As Long, ColNum As Long
Dim YesNo As String
Dim SheetName As String
Dim Sht As Worksheet

RowNum = Rng.Row
ColNum = Rng.Column
If Not (RowNum >= 27 And RowNum <= 61 And ColNum = 3) Then Exit Sub
YesNo = Rng
If YesNo <> "Yes" And YesNo <> "No" Then Exit Sub
SheetName = Rng.Offset(0, -1)
Set Sht = GetSheet(SheetName)
If Sht Is Nothing Then Exit Sub
If YesNo = "Yes" Then
If Sht.Visible = xlSheetVeryHidden Then
Sht.Visible = xlSheetHidden
End If
Sht.Visible = xlSheetVisible
If ActivateSheet Then
Sht.Activate
End If
Rng.Value = "No"
Else
If Sht.Visible = xlSheetVisible Then
Sht.Visible = xlSheetHidden
End If
Rng.Value = "Yes"
End If
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
ShowHideClick Target, True
End Sub

在包含Yes/No表的工作表中插入代码。