[µo°Ý] ½Ð±Ð»yªk¬O­þ¸Ì¥X°ÝÃD¡H

skyutm

¦U¦ì½×¾Âªº¥ý¶i¤j®a¦n¡A¤p§Ì±q¾Ç²ßvba»yªk¥H¨Ó±Nªñ¦³¥b­Ó¤ëªº®É¶¡¤F¡A¥Øªº¬O¬°¤F³]­p¤@®M¦¨ÁZ­pºâ¨t²Î¡C¦hÁ«½×¾Â¤W¦h¦ì¼ö¤ß¤H¤hªºÀ°¦£¡A±o¥H¸Ñ¨M³\¦h°ÝÃD¡C¤]¦]¬°¦p¦¹¡A©Ò¥H¦³¨Ç»yªk¤@ª¾¥b¸Ñ¡C¦³³Ò¦U¦ì¼ö¤ßªº¤j¤j¥X¤â¬Û§U¡C
        ¤p§Ì³Ìªñ¹J¨ì¤F¤@­Ó°ÝÃD¡A­ì¥»§Ú¤w¸g¼g¦n¤F»yªk¡A¥Øªº¬O«OÅ@sheet2©Msheet3ªº³¡¤À¸ê®Æ¡A¦Ó¥B«e´X¤Ñ¤]¤w¸g°õ¦æ¦¨¥\¡A¥i¬O¤µ¤Ñ¤S°õ¦æ®É¡A«o¤S¥X²{¤F¿ù»~°T®§¡uª«¥ó¤£¤ä´©¦¹ÄݩʩΤèªk¡v¦Ó¦¹¦C¤Ï¶À   .Selection.Locked = True  ¦Ñ¹ê»¡¡A§Ú¤]¤£ª¾­þ¸Ì¥X¿ù¡A©Ò¥H¨Ó³o¨à´M¨D¨ó§U¡A¬O¤£¬O¦Û¤v¦³¨Çª¼ÂI¨Sµo²{¡C
»yªk
Sub Worksheet_Activate()
    With Worksheets("´Á¤¤µû¶q")
    .Range("A1:v2").Select
    .Range("a3", cells(3, 1).End(xlDown)).Select
    .Selection.Locked = True
    .Selection.FormulaHidden = True
    .ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End With
    End Sub
Sub Worksheet_Deactivate()
    With Worksheets("´Á¤¤µû¶q")
    .cells.Select
    .Selection.Locked = False
    .Selection.FormulaHidden = True
    .ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End With
    End Sub

skyutm

§Ú¤S¾Ç¨ì¤@©Û¤F¡A§Ú¤@ª½¥H¬°¤¤¤å¦r­n¥[""¤~¦æ¡A¤£µM¹q¸£·|¥X²{¿ù»~¡I

c_c_lai

¦^´_ 24# skyutm
¨S¿ù¡IÅܼƩΪ̬O¨ç¦¡¬O¥i¥H¥H¤¤¤å¦r¨Óªí²{ªº¡A Ä´¦p¡G

1. Option Explicit
   
2. Public Ar, ¹Ïªí©³¦â
   
3. Public Const ¸ê®Æ¼Æ°Ï = "A8:F8"
   
4. 'Public Const ¹Ïªí©³¦â = 6
   
5. 
   
6. Private Sub Auto_Open()
   
7.     Sheets("Omega").Activate
   
8.     ±±¨î¶µ
   
9. End Sub
   
10. 
    
11. Private Sub Auto_Close()
    
12.     Sheets("Omega").Activate
    
13.     ±±¨î¶µ
    
14.     ThisWorkbook.Save
    
15. End Sub
    
16. 
    
17. Private Sub ±±¨î¶µ()
    
18.     ' ...........
    
19. End Sub

½Æ»s¥N½X

¬O¤ñ¸û©ö©ó²M´·©öÀ´¡A¦ý¦pªG¸Óµ{¦¡¥Î©ó¨ä¥¦°ê®a¦a°ìªº¸Ü
·|³y¦¨§xÂZªº (Åܦ¨¶Ã½X)¡C

skyutm

½Ð±Ð¤@¤U¡A³oºâ¬Ovbaªº»yªk¶Ü¡H¦]¬°¸Ì­±¦³¤¤¤å¦r¡H³o¬O§Ú±qvba±M®×¸ÌÀY½Æ»s¥X¨Óªº¤@³¡¥÷
Sub Worksheet_Deactivate()
     ­ì¦~¯Å = Val(Cells(12, 2))
     ·s¦~¯Å = Val(Cells(1, 18))
     Cells(12, 2) = ·s¦~¯Å
  If ·s¦~¯Å > 2 And Cells(14, 20) <> 0 Then '±j­¢3~6¦~¯Å®É,°ò¥»¤u§@ªí T14Àx¦s®æ­È¬°0
     Cells(14, 20) = 0
  End If

skyutm

¦^´_ 22# luhpro


    ®¦¡IªGµM¬Ý°_¨ÓµÎªA¦h¤F

luhpro

¦^´_ 21# skyutm
µ½¥Î With »P ÁY±Æ(¦¸¤@¯Å°Ï¶ô¤ºªºµ{¦¡¥ª¤è¦h´X­ÓªÅ®æ) ·|Åýµ{¦¡§ó®e©ö¾\Ū»PºûÅ@:

1. Sub Worksheet_Activate()
   
2.     '­Ó¤H¦¨ÁZ³æ¼ÐÃD¦C¡õ'
   
3.     Dim a, b, c, x, y
   
4.    
   
5.     With Sheets("°ò¥»³]©w")
   
6.       a = .Range("j1").Value
   
7.       b = .Range("j2").Value
   
8.       c = .Range("j3").Value
   
9.     End With
   
10.    
    
11.     d = Fix(c / 2)
    
12.    
    
13.     For i = 0 To d
    
14.       With Sheets("´Á¤¤µû¶q")
    
15.         x = .Range("a" & 3 + i * 2).Value
    
16.         y = .Range("b" & 3 + i * 2).Value
    
17.         x1 = .Range("a" & 4 + i * 2).Value
    
18.         y1 = .Range("b" & 4 + i * 2).Value
    
19.       End With
    
20.    
    
21.       With Sheets("´Á¤¤¦¨ÁZ³æ")
    
22.         .Range("a" & 1 + 12 * i).Value = a & "¦~" & b & "¯Z ´Á¤¤µû¶q ¦¨ÁZ³æ" & "(" & x & ")" & y
    
23.         .Range("o" & 1 + 12 * i).Value = a & "¦~" & b & "¯Z ´Á¤¤µû¶q ¦¨ÁZ³æ" & "(" & x1 & ")" & y1
    
24.       End With
    
25.     Next i
    
26.     '­Ó¤H¦¨ÁZ³æ¼ÐÃD¦C¡ô'
    
27. End Sub

½Æ»s¥N½X

skyutm

©êºp¡I¤À¨É¤@¤U¡A§Ú·Q¥X¨Óªº»yªk¬O³o¼Ë¡Aµ²ªG¸Õ¤F¤]¨S°ÝÃD¡C
Sub Worksheet_Activate()
    '­Ó¤H¦¨ÁZ³æ¼ÐÃD¦C¡õ'
    Dim a, b, c, x, y
    a = Sheets("°ò¥»³]©w").Range("j1").Value
    b = Sheets("°ò¥»³]©w").Range("j2").Value
    c = Sheets("°ò¥»³]©w").Range("j3").Value
    d = Fix(c / 2)
    For i = 0 To d
    x = Sheets("´Á¤¤µû¶q").Range("a" & 3 + i * 2).Value
    y = Sheets("´Á¤¤µû¶q").Range("b" & 3 + i * 2).Value
    x1 = Sheets("´Á¤¤µû¶q").Range("a" & 4 + i * 2).Value
    y1 = Sheets("´Á¤¤µû¶q").Range("b" & 4 + i * 2).Value
    Sheets("´Á¤¤¦¨ÁZ³æ").Range("a" & 1 + 12 * i).Value = a & "¦~" & b & "¯Z ´Á¤¤µû¶q ¦¨ÁZ³æ" & "(" & x & ")" & y
    Sheets("´Á¤¤¦¨ÁZ³æ").Range("o" & 1 + 12 * i).Value = a & "¦~" & b & "¯Z ´Á¤¤µû¶q ¦¨ÁZ³æ" & "(" & x1 & ")" & y1
    Next i
    '­Ó¤H¦¨ÁZ³æ¼ÐÃD¦C¡ô'
End Sub

skyutm

¦^´_ 19# GBKEE
³ø§iª©¥D¤j¤H¡A¤p§Ì§Ú¤S¹J¨ì²~ÀV¤F¡A¦]¬°¦b­Ó¤H¦¨ÁZ³æ¿é¥X¤è­±¹J¨ì°ÝÃD¡AÁ`¦¨ÁZ³æ¤w¸g§¹¦¨¡A¤]´N¬O»¡¸ê®Æ¦p¤U¡G
®y¸¹  ©m    ¦W  °ê»y  ­^»y  ¼Æ¾Ç  ¦ÛµM  ªÀ·|
001  ¬Y¬Y¬Y   90      100    85      75     90
¦ý¦b¦¸­¶ªº­Ó¤H¦¨ÁZ³æ¡A§Ú·Q¿é¥X¦¨¬°a4­¶¤j¤p¡A¨C­¶¦³8µ§¸ê®Æ¡A§Úª¾¹D³Ì²³æ«o³Â·Ðªº¤èªk¡A´N¬O§â¨Cµ§¦¨ÁZ¡A§Q¥Î¬Û¹ïÀ³ªº¦ì¸m¡A¿é¤J¨ì­Ó¤H¦¨ÁZÄæ¦ì¡A¦ý¬OµLªk±±¨î¸ê®Æªº¼Æ¶q¡A¦]¬°¦³´Xµ§¦¨ÁZ¡A¬O¥Ñ¡u°ò¥»³]©w¡v¤u§@ªíªº¯Z¯Å¤H¼ÆÄæ±±¨îªº¡A¸Ó«ç»ò¿ì©O¡H¦³¨S¦³¨ä¥Lªººc·Q©O¡Hªþ¤WÀÉ®×¥H«K°Ñ¦Ò¡I

GBKEE

¦^´_ 18# skyutm
Application.Transpose(ar) ¬O¨Ï¥Î¤u§@ªí Âà¸m¨ç¼Æ  ¥»©« 10# ¤w¦³¤¶²Ð¦p¦ó¨Ï¥Î¤u§@ªí
¹ï©ó¤£À´ªºVBA  ¤èªk, ÄÝ©Ê, ¨ç¼Æ ¥i¦h¬Ý VBA¤¤ªº»¡©ú½m²ß¥¦ªº½d¨Ò ·|¶i¨Bªº
³o¦³¾ã²z   ·s¤â VBA ¤Jªù±`¨£°ÝÃD

skyutm

¦^´_ 17# GBKEE
©êºp¤S¥´ÂZ¤F¡I¦]¬°³Ìªñ¹J¨ì¤@¨Çµ²¸Ñ¤£¶}¡C©Ò¥H´N§â»yªk­«·s¾ã²z¤@¤U¡A¦]¬°·íªì¦h¬O¾a§O¤HÀ°¦£ªº¡A©Ò¥H¦³¨Ç¤@ª¾¥b¸Ñ¡Aª¾¹Dª©¥D¥\¤O²`«p¡A¯S¨Ó½Ð±Ð¤@¤U¡A¦p¦³¥pÂZ¡AÁٽШ£½Ì¡C´N¬O³o¤@¥y
    '´Á¤¤µû¶q¦Û°Ê¿é¤J®y¸¹¡õ'  ¡]³o§Úª¾¹D¡^
    If Intersect(Target, [J1:J3]) Is Nothing Or [j3] = 0 Then Exit Sub  ¡]§Ú¥uª¾¹D«á­±¡G¦pªG¨S¶ñ¸ê®Æ©Îj3¬O0 »yªk´N¤£°õ¦æ¡A¦ý«e­±¤£À´¡^
    ReDim ar(1 To [j3])    ¡]¤£À´¡^
    YC = [j1] & Format([j2], "00")   ¡]¤£À´¡^
    For i = 1 To [j3]   ¡]ÁA¸Ñ¡^
    ar(i) = YC & Format(i, "00")  ¡]¤£À´¡^
    Next   ¡]ÁA¸Ñ¡^
    With Sheets("´Á¤¤µû¶q")    ¡]ÁA¸Ñ¡^
    .Cells.Rows.Hidden = False  ¡]©Ò¦³Àx¦s®æ¤£ÁôÂá^
    .Cells.Columns.Hidden = False  ¡]©Ò¦³Àx¦s®æ¤£ÁôÂá^
    .[A:A] = ""  ¡]¤£À´¡^
    .[a3].Resize([j3], 1) = Application.Transpose(ar)  ¡]¤£À´¡^
    .Range("W1", .Cells(1, .Columns.Count)).EntireColumn.Hidden = True¡]w1¨ìÁ`­p´X¦C³£ÁôÂá^
    .Range(.[a3].Offset([j3], 0), .Cells(Rows.Count, 1)).EntireRow.Hidden = True  ¡]¤£À´¡^
    .Range("a1").Value = "®y¸¹"   ¡]ÁA¸Ñ¡^
    End With   ¡]ÁA¸Ñ¡^
    '´Á¤¤µû¶q¦Û°Ê¿é¤J®y¸¹¡ô'  ¡]ÁA¸Ñ¡^