自動隱藏活頁薄
各位大大請問如何可以在sheets "HS BANK BOOK #395" 的C3 儲存格輸入"open" 就可以自動將"PV,RV,JV,COA"的工作表自動浮現,
如果C3 儲存格不是"open"的字就會自動非常隱藏?
謝謝 Private Sub Worksheet_Change(ByVal Target As Range)
Dim xS As Worksheet, V
With Target
If .Address <> "$C$3" Then Exit Sub
V = xlSheetVeryHidden
If UCase(.Value) = "OPEN" Then V = xlSheetVisible
Application.ScreenUpdating = False
For Each xS In Sheets(Array("PV", "RV", "JV", "COA"))
If xS.Visible <> V Then xS.Visible = V
Next
End With
End Sub [i=s] 本帖最後由 mdr0465 於 2021-11-6 18:18 編輯 [/i]
[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=117610&ptid=23470]2#[/url] [i]准提部林[/i] [/b]
准提部,師兄你好
謝謝你的回覆,
當我輸入"open" 是沒有問題可以隱藏工作表, 但當我在儲存格沒有"open"的文字時就會出現error 請問怎樣可以修改呢?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xS As Worksheet, V
With Target
If .Address <> "$C$1" Then Exit Sub
V = xlSheetVeryHidden
If UCase(.Value) = "OPEN" Then V = xlSheetVisible
Application.ScreenUpdating = False
For Each xS In Sheets(Array("PV", "RV", "JV", "COA"))
If xS.Visible <> V Then [color=Blue]xS.Visible = V[/color] [color=Blue]' 問題出現在這一句[/color]
Next
End With
End Sub
謝謝 [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=117616&ptid=23470]3#[/url] [i]mdr0465[/i] [/b]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xS As Worksheet, V
With Target
If .Address <> "$C$1" Then Exit Sub
V = 2
If UCase(.Value) = "OPEN" Then V = -1
Application.ScreenUpdating = False
For Each xS In Sheets(Array("PV", "RV", "JV", "COA"))
If xS.Visible <> V Then xS.Visible = V ' 問題出現在這一句
Next
End With
End Sub [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=117619&ptid=23470]4#[/url] [i]准提部林[/i] [/b]
准提部林, 你好
可以了, 謝謝你幫忙 [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=117619&ptid=23470]4#[/url] [i]准提部林[/i] [/b]
准提部林,你好
如果我想用同一方法, 但是想將"C3:C100" 的儲存格內里如有數字輸入就將不夠7位數目字的儲存格, 就算動將數字前面補"0" 可以怎樣修改呢?
比如"1233" >>>"0001233"
謝謝 [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=117627&ptid=23470]6#[/url] [i]mdr0465[/i] [/b]
格式設成 0000000 不可以嗎? [quote]回復 mdr0465
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xS As Worksheet, V
With ...
[size=2][color=#999999]准提部林 發表於 2021-11-6 19:35[/color] [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=117619&ptid=23470][img]http://forum.twbts.com/images/common/back.gif[/img][/url][/size][/quote]
准提部林,你好
為何我過了一段日子再開啟會失效呢? [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=117928&ptid=23470]8#[/url] [i]mdr0465[/i] [/b]
有密碼，無法得知問題，謝謝 [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=117930&ptid=23470]9#[/url] [i]samwang[/i] [/b]
Samwang 師兄
請幫忙查看,謝謝 [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=117933&ptid=23470]10#[/url] [i]mdr0465[/i] [/b]
是如下那種功能需求嗎? 請測試看看，謝謝
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xS As Worksheet, V
With Target
If Target.Address = "$D$1" Then
If UCase(.Value) = "OPEN" Then V = 1 Else V = 0
For i = 1 To Sheets.Count
With Sheets(i)
If .Name = "PV" Or .Name = "RV" Or .Name = "JV" Or .Name = "COA" Then
.Visible = V
End If
End With
Next
End If
End With
End Sub [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=117934&ptid=23470]11#[/url] [i]samwang[/i] [/b]
Samwang
可以了, 謝謝你的幫忙
頁:
[1]