[發問] 工作表顯示與隱藏

henry860608

以撰寫程式碼如下，第一次點選右鍵可以隱藏單一工作表，第二次點選右鍵卻隱藏所有工作表，請問問題在哪裡?
程式碼與附件如下

1. Private Sub Worksheet_Activate()
   
2. Dim i, n As Integer
   
3. Dim a As String
   
4.     n = Sheets.Count
   
5. For i = 1 To n
   
6.     If Sheets(i).Name <> Cells(i + 1, 2) Then
   
7.         a = Cells(i + 1, 2)
   
8.         If a = "" Then Exit Sub
   
9.         Sheets(i).Name = a
   
10.     End If
    
11. Next
    
12. End Sub
    
13. 
    
14. Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
15. 
    
16. End Sub
    
17. 
    
18. 
    
19. Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    
20. Dim i, n As Integer
    
21.     n = Sheets.Count
    
22. For i = 2 To n
    
23. If Target = Cells(i + 1, 4) Then
    
24.     If Cells(i + 1, 4).Interior.Color = vbBlue Then
    
25.         Cells(i + 1, 4) = "顯示工作表"
    
26.         Cells(i + 1, 4).Interior.Color = vbRed
    
27.         Worksheets(i).Visible = False
    
28.     Else
    
29.         Cells(i + 1, 4) = "隱藏工作表"
    
30.         Cells(i + 1, 4).Interior.Color = vbBlue
    
31.         Worksheets(i).Visible = True
    
32.     End If
    
33. Else
    
34. 
    
35. End If
    
36. Next
    
37. End Sub

複製代碼

1563740111775.zip (16.43 KB)

附件
下載次數: 13

2019-7-22 14:50

henry860608

代碼已經更新了，但是還是發生點選一個工作表，卻隱藏兩個工作表。

1. Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
   
2. Dim i, n As Integer
   
3.     n = Sheets.Count
   
4. For i = 2 To n
   
5. If Target = Cells(i, 4) Then
   
6. 
   
7.     If Cells(i, 4).Interior.Color = vbBlue Then
   
8.         Cells(i, 4) = "顯示工作表"
   
9.         Cells(i, 4).Interior.Color = vbRed
   
10.         Worksheets(i - 1).Visible = xlSheetVisible
    
11.     Else
    
12.         Cells(i, 4) = "隱藏工作表"
    
13.         Cells(i, 4).Interior.Color = vbBlue
    
14.         Worksheets(i - 1).Visible = xlSheetVeryHidden
    
15.     End If
    
16. Else
    
17.    
    
18. End If
    
19. Next
    
20. End Sub

複製代碼

henry860608

下載 (170.18 KB)
如圖所示

2019-7-22 23:37

准提部林

看不懂要做什麼???
跑迴圈, 當然會多張工作表顯示或隱藏~~

henry860608

想法:可透過點選來改變工作表的顯示與隱藏特性

henry860608

已經找到問題了，利用with selection 即可，不需要迴圈函數

1. Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
   
2. On Error Resume Next
   
3.     With Selection(1)
   
4.       If Target = Cells(.Row, 4) Then
   
5.         If Cells(.Row, 4).Interior.Color = vbBlue Then
   
6.             Cells(.Row, 4) = "顯示工作表"
   
7.             Cells(.Row, 4).Interior.Color = vbRed
   
8.             Worksheets(.Row - 1).Visible = xlSheetVisible
   
9.         ElseIf Cells(.Row, 4).Interior.Color = vbRed Then
   
10.             Cells(.Row, 4) = "隱藏工作表"
    
11.             Cells(.Row, 4).Interior.Color = vbBlue
    
12.             Worksheets(.Row - 1).Visible = xlSheetVeryHidden
    
13.         Else
    
14.         End If
    
15.     End If
    
16.     End With
    
17.    
    
18. End Sub

複製代碼

准提部林

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
With Cells(Target(1).Row, 2)
     On Error GoTo 999
     CK% = Sheets(.Value).Visible + 1
     Sheets(.Value).Visible = CK
     .Cells(1, 3) = Array("顯示工作表", "隱藏工作表")(CK)
     .Cells(1, 3).Interior.Color = Array(vbRed, vbBlue)(CK)
End With
999: End Sub


=============================

henry860608

回復 7# 准提部林
謝謝大大，如果要改成 xlSheetVeryHidden 要怎麼做

准提部林

回復 8# henry860608

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
With Cells(Target(1).Row, 2)
     On Error GoTo 999
     ck% = Abs(1 - Sheets(.Value).Visible)
     Sheets(.Value).Visible = ck
     .Cells(1, 3) = Array("隱藏工作表", "顯示工作表")(ck - 1)
     .Cells(1, 3).Interior.Color = Array(vbBlue, vbRed)(ck - 1)
End With
999: End Sub

自行研究下:
xlSheetVisible = -1
xlSheetHidden = 0
xlSheetVeryHidden = 2

========================

henry860608

回復 9# 准提部林
謝謝准提部林，每次看到你的程式都讓我很驚訝
精簡而且容易了解，感恩