返回列表 上一主題 發帖

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

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

以撰寫程式碼如下,第一次點選右鍵可以隱藏單一工作表,第二次點選右鍵卻隱藏所有工作表,請問問題在哪裡?
程式碼與附件如下
  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. Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

  14. End Sub


  15. Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
  16. Dim i, n As Integer
  17.     n = Sheets.Count
  18. For i = 2 To n
  19. If Target = Cells(i + 1, 4) Then
  20.     If Cells(i + 1, 4).Interior.Color = vbBlue Then
  21.         Cells(i + 1, 4) = "顯示工作表"
  22.         Cells(i + 1, 4).Interior.Color = vbRed
  23.         Worksheets(i).Visible = False
  24.     Else
  25.         Cells(i + 1, 4) = "隱藏工作表"
  26.         Cells(i + 1, 4).Interior.Color = vbBlue
  27.         Worksheets(i).Visible = True
  28.     End If
  29. Else

  30. End If
  31. Next
  32. End Sub
複製代碼
1563740111775.zip (16.43 KB)

NEW: 工作表顯示與隱藏

代碼已經更新了,但是還是發生點選一個工作表,卻隱藏兩個工作表。
  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.     If Cells(i, 4).Interior.Color = vbBlue Then
  7.         Cells(i, 4) = "顯示工作表"
  8.         Cells(i, 4).Interior.Color = vbRed
  9.         Worksheets(i - 1).Visible = xlSheetVisible
  10.     Else
  11.         Cells(i, 4) = "隱藏工作表"
  12.         Cells(i, 4).Interior.Color = vbBlue
  13.         Worksheets(i - 1).Visible = xlSheetVeryHidden
  14.     End If
  15. Else
  16.    
  17. End If
  18. Next
  19. End Sub
複製代碼

TOP

如圖所示

1563809548299.gif
2019-7-22 23:37

TOP

看不懂要做什麼???
跑迴圈, 當然會多張工作表顯示或隱藏~~
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

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

TOP

Sol: 工作表顯示與隱藏

已經找到問題了,利用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
複製代碼

TOP

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


=============================
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

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

TOP

回復 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

========================
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

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

TOP

        靜思自在 : 並非有錢魷是快樂,問心無愧心最安。
返回列表 上一主題