vba - VBA hide and show sheets based on YES/NO and worksheet name
问题描述
I have a workbook with many sheets. I have an index worksheet that list the names of the worksheets in column B and a Yes/No question in column c. I want a code that when I click "YES" it will open the worksheet that it corresponds to. When I click "NO" it will hide the worksheet. The names in the list exactly match the name of the worksheets. I presume it will be with a loop?
Range is from B27 to C61
解决方案
If we assume column 2 contains the name of the sheet and column 3 contains either Yes or No, then this code will do it, using double-click:
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
Insert the code in the woorksheet containing the Yes/No table.
推荐阅读
- function - Flutter - 使用动态函数来控制 UI
- heroku - 将 Flask 应用程序部署到 Heroku 时出现奇怪的“is_xhr”错误
- google-sheets - 在日期数组中搜索最低/最高值
- python - 类型对象“SGP30”没有属性“关闭”
- android - Android Studio - 从 EditText 视图获取输入
- python - 如何将合并的数据从单元格中提取到 python 数据框中的行中?
- javascript - Rails & AJAX,是否有理由不直接在控制器操作中呈现 html 视图以供 ajax 处理?
- java - 标记无法使用 JUnit5 在 IntelliJ 中运行测试
- android - Spotify SDK 在已通过身份验证后摆脱身份验证弹出窗口
- python - Python:意外分配 repr 一个 int 值