lee88

¦^´_ 1# goner


  
  

¤U¸ü (39.59 KB)

2022-12-27 14:25

¨Ì§Aªº´£°Ý ¦p¤U¹Ï¤ù ¦b½d³ò¤ºªº¹B§@¬Ý¬Ý

¤U¸ü (43.48 KB)

2022-12-27 14:26

Á`ªíªºµ{¦¡½X¦p¤U

1. Option Explicit
   
2. Private Sub CommandButton1_Click()  '¨ú®øÁôÂÃ¥þ³¡¤u§@ªíªº«öÁäµ{¦¡½X
   
3.         Dim Sh As Worksheet
   
4.         Application.ScreenUpdating = False
   
5.         For Each Sh In Sheets
   
6.             Sh.Visible = True
   
7.         Next
   
8.         Application.ScreenUpdating = True
   
9. End Sub
   
10. Private Sub Worksheet_Change(ByVal Target As Range)
    
11.         If Target.Count = 1 Then HideSheet Target
    
12. End Sub
    
13. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
14.     If Target.Count = 1 Then HideSheet Target
    
15. End Sub
    
16. Sub HideSheet(Target As Range)
    
17.     Dim Rng As Range, A²Õ As String, E As Variant
    
18.     If Application.Intersect(Range("A1").CurrentRegion, Target) Is Nothing Then Exit Sub
    
19.     Application.EnableEvents = False       '¤£Ä²°Ê Worksheet¹w³]¨Æ¥óµ{¦¡
    
20.     On Error GoTo Err                                    'µ{¦¡²£¥Í¿ù»~®É²¾¨ì ¼Ð°OÂI
    
21.     With Range("A1").CurrentRegion
    
22.         Set Rng = Range(.Cells(2, Target.Column), .Cells(.Rows.Count, Target.Column))
    
23.     End With
    
24.     A²Õ = "Á`ªí"    'A²Õ = "Á`ªí,ªí1,ªí2,ªí3"    ' ***¥i­×§ï ¥²¶·Åã¥Üªº¤u§@ªí
    
25.     For Each E In Rng
    
26.         A²Õ = A²Õ & "," & E
    
27.         Set Target = E
    
28.         Debug.Print TypeName(Sheets(E.Value)) ' '´ú¸ÕÀx¦s®æªº¤u§@ªí¦WºÙ¬O§_¦s
    
29.     Next
    
30.     A²Õ = "," & A²Õ & "," '
    
31.     Application.ScreenUpdating = False
    
32.     For Each E In Sheets
    
33.             E.Visible = InStr(UCase(A²Õ), "," & UCase(E.Name) & ",") > 0
    
34.             '* ¤ñ¹ï§¹¥þ¬Û¦Pªº¤u§@ªí¦WºÙ ->>     "," & UCase(i.Name) & ","
    
35.     Next
    
36.     Application.ScreenUpdating = True
    
37.     Application.EnableEvents = True      '¦^´_IJ°Ê Worksheet¹w³]¨Æ¥óµ{¦¡
    
38.     Exit Sub
    
39. Err:                          '³B²zµ{¦¡½Xªº¿ù»~
    
40.     MsgBox IIf(Target <> "", "  §ä¤£¨ì ¤u§@ªí [" & Target & "]", Target.Address(0, 0) & "¨S¦³¿é¤J....")
    
41.     Application.EnableEvents = True      '¦^´_IJ°Ê Worksheet¹w³]¨Æ¥óµ{¦¡
    
42. End Sub

½Æ»s¥N½X