我有一本作業簿，里面有很多張紙。我有一個索引作業表，其中列出了 B 列中作業表的名稱和 c 列中的是/否問題。我想要一個代碼，當我單擊“是”時，它將打開它對應的作業表。當我單擊“否”時，它將隱藏作業表。串列中的名稱與作業表的名稱完全匹配。我想它會有一個回圈？
范圍是從 B27 到 C61
uj5u.com熱心網友回復：
如果我們假設第 2 列包含作業表的名稱，第 3 列包含是或否，則此代碼將執行此操作，使用雙擊：
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
在包含是/否表的作業表中插入代碼。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/335741.html