ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2022-12-22 18:20 ½s¿è

°µ­Ó¼ËªO//¦U²Õ¤u§@ªí¥i¥ô·N²Õ¦X
¶Ç¿ùÀÉ//¬Ý¤U¤U¼Ó

TOP

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


ª©¥D¤j¤j¡Aªþ¥[ÀÉÀ³¸Ó¬O¥DÃD¬° [²Å¦X¦h±ø¥ó±a¥X¬ÛÃö¸ê°T] ªº¸Ñµª­ò¡C

«á¾Ç¬Ý¤F¤º³¡¤½¦¡¦³¥]§t¯S©w¤å¦rªº±ø¥ó¿z¿ï¤½¦¡«Ü¦n¥Î¡A¤w¦¬ÂáCÁÂÁÂÅo¡C

TOP

¦^´_ 12# goner

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

TOP

¦^´_ 9# Andy2483

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

TOP

¦^´_ 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
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-12-23 11:09 ½s¿è

¦^´_ 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
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

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


    ÁÂÁ«e½ú«ü¾É§â¤u§@ªíªºÅã¥Ü©ÎÁôÂåΥ¬ªLÅܼƱ±¨î,¬Ý±oÀ´,¦ý¬O«á¾Ç»Ý­n§ó§V¤O¾Ç²ß¤~¦³¾÷·|À³¥Î,«á¾Ç¥H«á¹J¨ìª«¥óªº0/1Åܤƥi¥H¦^¨Ó¬Ý³o©«
¦A¦¸ÁÂÁ«e½ú
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 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

TOP

¦^´_ 1# goner


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

Á`ªíªºµ{¦¡½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

TOP

¦^´_ 19# lee88


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

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


¤u§@ªí¼Ò²Õ:


Module¼Ò²Õ:


´ú¸Õµ²ªG:


§ä¤£¨ì:
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : «H¤ß¡B¼Ý¤O¡B«i®ð¤TªÌ¨ã³Æ¡A«h¤Ñ¤U¨S¦³°µ¤£¦¨ªº¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD