[µo°Ý] ¦bVBA¤¤¡A¦p¦ó¨Ï¥Î¥¨¶°ÁôÂó¡¥÷¤u§@ªí¡B¤Î¨ú®øÁôÂÃ¥þ³¡¤u§@ªí¡H

goner

½Ð°Ý¦U¦ì¥ý¶i¦bVBA¤¤¡A¦p¦ó¨Ï¥Î¥¨¶°ÁôÂó¡¥÷¤u§@ªí¡B¤Î¨ú®øÁôÂÃ¥þ³¡¤u§@ªí¡H
°²³]¦³15­Ó¤u§@ªí¡AA²Õ1.2.3¤u§@ªí¡BB²Õ4.5.6¤u§@ªí¡BC²Õ7.8.9¤u§@ªí¡BD²Õ10.11.12¤u§@ªí¡BE²Õ13.14.15
¥¨¶°1¡GA¡BB¤u§@ªí¤£ÁôÂáA¨ä¥LC¡BD¡BE¤u§@ªíÁôÂáC
¥¨¶°2¡GA¡BC¤u§@ªí¤£ÁôÂáA¨ä¥LB¡BD¡BE¤u§@ªíÁôÂáC
¥¨¶°3¡GA¡BD¤u§@ªí¤£ÁôÂáA¨ä¥LB¡BC¡BE¤u§@ªíÁôÂáC
¥¨¶°4¡GA¡BE¤u§@ªí¤£ÁôÂáA¨ä¥LB¡BC¡BD¤u§@ªíÁôÂáC

¦³§ä¨ìÁôÂÃ/¨ú®øÁôÂêºVBA¡A¦ý¤£ª¾¦p¦ó°õ¦æ³¡¥÷¡B¥þ³¡
Sub UnhideSheet()
       Sheets("Sheet1").Visible = True
End Sub

Sub HideSheet()
       Sheets("Sheet1").Visible = False
End Sub

½Ð¦U¦ì¥ý¶i¤£§[«ü±Ð¡CÁÂÁ¡C

goner

¦^´_ 19# lee88


­ì¨ÓÁÙ¦³³o»ò¦h¤èªk¡A¯uÁÂÁ«ü¾É¡AÁÂÁ´£¨Ñ¤£¤@¼Ëªº¨Ï¥Î¤è¦¡¡C

¯uªº«Ü¦h°ª¤â^^

EXCELªºVBA·|¶Vª±¶V¦nª±¡A¦ü¥G¤°»ò¥\¯à³£¥i¥H¹F¦¨¡A¥u¥i±¤¦n¹³¤£¯àÂà¥X¦¨EXEÀÉ¡C

Andy2483

¦^´_ 19# lee88


    °ª¤â¶³¶°ªº½×¾Â
ÁÂÁ«e½ú,«á¾ÇÂǦ¹©«¾Ç²ß¨ì«Ü¦hª¾ÃÑ,¾Ç²ß¤ß±o¦p¤U,½Ð«e½ú¦A«ü¾É

Á`ªí_¤u§@ªí©ú²Ó:

¤U¸ü (52.53 KB)

2022-12-27 16:25

¤u§@ªí¼Ò²Õ:

¤U¸ü (104.67 KB)

2022-12-27 16:26

Module¼Ò²Õ:

¤U¸ü (181.94 KB)

2022-12-27 16:27

´ú¸Õµ²ªG:

¤U¸ü (74.08 KB)

2022-12-27 16:27

§ä¤£¨ì:

¤U¸ü (46.97 KB)

2022-12-27 16:29

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

goner

¦^´_ 13# ­ã´£³¡ªL


«z¡I«W¸¨ªº±±¨î¡A¤£¥Î¶}¶}ÃöÃöªº¡A³o¼Ëªº¼gªk±±¨î¦ü¥GÅý°õ¦æ³t«×Åܧ֤F¡A¥B¤£®e©ö¿ù»~¡C

·PÁ ­ã´£³¡ªL ª©¥D¡A¤]ÁÂÁ Andy ªº¼ö¤ßª`ÄÀ¡C

³oÀ°§U«Ü¦h¡AÁÂÁ¡C

Andy2483

¦^´_ 13# ­ã´£³¡ªL


    ÁÂÁ«e½ú«ü¾É§â¤u§@ªíªºÅã¥Ü©ÎÁôÂåΥ¬ªLÅܼƱ±¨î,¬Ý±oÀ´,¦ý¬O«á¾Ç»Ý­n§ó§V¤O¾Ç²ß¤~¦³¾÷·|À³¥Î,«á¾Ç¥H«á¹J¨ìª«¥óªº0/1Åܤƥi¥H¦^¨Ó¬Ý³o©«
¦A¦¸ÁÂÁ«e½ú

Andy2483

¦^´_ 13# ­ã´£³¡ªL


    ÁÂÁ«e½ú«ü¾É
«á¾Ç¦b¦¹½d¨Ò¾Ç²ß¨ì«Ü¦hª¾ÃÑ,½d¨Ò¤ß±oµù¸Ñ¦p¤U,½Ð«e½ú¦A«ü¾É,ÁÂÁÂ

Sub TEST_A1()
Dim S(5), Nx, SS$, Sht As Worksheet, Y As Boolean
'¡ô«Å§iÅܼÆ:S¬O¤@ºû°}¦CS(0)~S(5),Nx¬O³q¥Î«¬ÅܼÆ,Sht¬O¤u§@ªíÅܼÆ,SS¬O¦r¦êÅܼÆ
'Y¬O¥¬ªLÅܼÆ
Nx = InputBox("½Ð¿é¤J 0~4 ¼Æ¦r!", "¤u§@ªíÅã¥ÜÁôÂñ±¨î", 0)
'¡ô¥ONx³o³q¥Î«¬ÅܼƬOInputBox() ¨ç¦¡¦^¶Ç­È
'https://learn.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/inputbox-function
If InStr("/1/2/3/4/0/", Nx) < 2 Then Exit Sub
'¡ô¦pªGNx³q¥Î«¬ÅܼƭȥΠInStr()¨ç¦¡¦^¶Ç­È<2 ,´Nµ²§ôµ{¦¡°õ¦æ
S(0) = "/Á`ªí/ªí1/ªí2/ªí3/"
'¡ô¥OS°}¦C¯Á¤Þ¸¹0ªº¦r¦ê¬OÂù¤Þ¸¹¥]§¨ªº¤å¦r²Å¸¹
S(1) = "/Sheet4/Sheet5/Sheet6/"
'¡ôÃþ±À
S(2) = "/Sheet7/Sheet8/"
'¡ôÃþ±À
S(3) = "/Sheet9/Sheet10/Sheet11/Sheet12/"
'¡ôÃþ±À
S(4) = "/Sheet13/Sheet14/Sheet15/"
'¡ôÃþ±À
SS = S(0) & S(Nx):
'¡ô¥OSS³o¦r¦êÅܼƬO ¯Á¤Þ¸¹0ªºS°}¦C­È ³s±µ¯Á¤Þ¸¹¬O NxÅܼƪºS°}¦C­È
'¦pªG·Q·s¼W¥[¤u§@ªí³£¬O­nÅã¥Üªº,´N¦bS(0)¸Ì·Ó³W«h²K¥[
'¦pªG·Q·s¼W¥[¤u§@ªíÀH»Ý¨D¸s²ÕÅã¥Ü/ÁôÂêº,´N¦bS(1)~S(4)¸Ì·Ó³W«h²K¥[
For Each Sht In Sheets
'¡ô³]¶¶°j°é!¥OSht ¬O¤u§@ªí¤§¤@,±q«e­±ªí½ü¨ì«á­±ªí
    Y = False
    '¡ôY³o¥¬ªLÅܼƬO False
    If Nx = 0 Or InStr(SS, "/" & Sht.Name & "/") > 0 Then Y = True
    '¡ô¦pªGNxÅܼƬO 0,©Î¥Î InStr()¨ç¦¡¦^¶Ç­È¤j©ó 0 ??,
    'InStr(¦pªG¬Ù²¤¡A·|±q²Ä¤@­Ó¦r¤¸¦ì¸m¶}©l·j´M , SS¦r¦êÅܼÆ, Sht°j°é¤u§@ªí¦W«e«á¥]§¨"/"²Å¸¹)
    'https://learn.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/instr-function
    'if±ø¥ó¦¨¥ß!´NÅýY¥¬ªLÅܼƬO True
    Sht.Visible = Y
    '¡ô¥OSht°j°é¤u§@ªºÅã¥Ü©ÎÁôÂÃÀH Y¥¬ªLÅܼưµÅܤÆ
   '¦pªG¿é¤J 0 ·|¥þ³¡Åã¥Ü!!
Next
End Sub

Andy2483

¦^´_ 14# Emily


    ÁÂÁ«e½ú
111¸ò«á¾Ç«Ü¤[¤F,«pÁy¥Ö²Ä1,¿ù»~³Ì¦h²Ä1,°ÝÃD³Ì¦h²Ä1,½Ð¤£­n¶û±ó¥L
ÁÂÁ«e½ú´£¿ô«á¾Ç¾Ç²ß¦hÂI¼Ö½ì,¤]¥i¥H§ó¿ô¥Ø§ä¨ì¥Ø¼Ð
«á¾Ç¥Î³o©«Åwªï«e½ú,½Ð«e½ú¦h¦h«ü±Ð
http://forum.twbts.com/thread-23851-1-1.html
Hi

Emily

¦^´_ 9# Andy2483

­Ó¤H»{¬°¥Î¤@¨Ç¦³·N«äªº label ¥N´À 111 ¤ñ¸û¦n
¦p Skip, NoGroupSelection
§Y¬O
Goto Skip
.
.
Skip:

­ã´£³¡ªL

¦^´_ 12# goner

¤W¿ùÀɤF//
xx0001.rar (10.41 KB)

¤U¸ü¦¸¼Æ: 16

2022-12-22 18:19