skyutm

©êºp¤S¨Ó¥´ÂZ¤F¡A³oÀ³¸Ó¬O²³æªº»yªk¡A¥i¯à¬O§Ú¸£³U¥´µ²¤F¡A©Ò¥H¤@ª½µLªk°õ¦æ¡Aºc·Q¦p¤U¡Fsheet1(°ò¥»³]©w)¬O³]©w¬ì¥Ø¥[Åv¡AµM«ásheet2¡]´Á¤¤µû¶q¡^ªº¼ÐÃD¦C¡A§Ú·Q¨q¥X¦U¬ìªº¥[Åv¤ñ¨Ò¡A¦p°ê»y¥[Åv¬O7¡A§Ú·Q¨q¥X°ê»yx7¡A¦ý¬O¥¢±Ñ¤F¡A¤£ª¾»yªk­þ¸Ì¥X°ÝÃD¡A¦A³Â·Ð¦U¦ì¤F¡C   
   
    '¤ë¦Ò¬ì¥Ø¥[Åv'
    Dim chall As Integer, enall As Integer, maall As Integer, naall As Integer, soall As Integer
    chall = Worksheets("°ò¥»³]©w").Range("d2").Value
    enall = Worksheets("°ò¥»³]©w").Range("d3").Value
    maall = Worksheets("°ò¥»³]©w").Range("d4").Value
    naall = Worksheets("°ò¥»³]©w").Range("d5").Value
    soall = Worksheets("°ò¥»³]©w").Range("d6").Value
    Worksheets("´Á¤¤µû¶q").Range("c2").Value = "°ê»yx" & "chall"
    Worksheets("´Á¤¤µû¶q").Range("d2").Value = "­^»yx" & "enall"
    Worksheets("´Á¤¤µû¶q").Range("e2").Value = "¼Æ¾Çx" & "maall"
    Worksheets("´Á¤¤µû¶q").Range("f2").Value = "¦ÛµMx" & "naall"
    Worksheets("´Á¤¤µû¶q").Range("g2").Value = "ªÀ·|x" & "soall"

¡]»P¦Ò¤H¼Æ¡^¬O½Õ¬d¦³´X¤H°Ñ¥[¦Ò¸Õ¡A©Ò¥H§Ú·Q¥X¤F¤U¦Cªº»yªk¡A¬Ý¦ü¨S°ÝÃD¡Aµ²ªG°õ¦æ®É¡A¦pªG¶}ÀY²Ä¤@­Ó¸ê®ÆÄæ¦ì¡]¦pc3¡Bd3¡^¥u­n¬OªÅªº¡A¹q¸£´N·|Åã¥Ü§ä¤£¨ìÀx¦s®æ¡A¦pªG¦³¿é¤J´N¤£·|¡A­þ£°¦w®º¡H

    '»P¦Ò¤H¼Æ'
    Dim aa As Integer, bb As Integer, cc As Integer, dd As Integer, ee As Integer
    aa = Worksheets("´Á¤¤µû¶q").Range("c3", Range("c3").End(xlDown)).SpecialCells(xlCellTypeConstants, xlNumbers).Count
    bb = Worksheets("´Á¤¤µû¶q").Range("d3", Range("d3").End(xlDown)).SpecialCells(xlCellTypeConstants, xlNumbers).Count
    cc = Worksheets("´Á¤¤µû¶q").Range("e3", Range("e3").End(xlDown)).SpecialCells(xlCellTypeConstants, xlNumbers).Count
    dd = Worksheets("´Á¤¤µû¶q").Range("f3", Range("f3").End(xlDown)).SpecialCells(xlCellTypeConstants, xlNumbers).Count
    ee = Worksheets("´Á¤¤µû¶q").Range("g3", Range("g3").End(xlDown)).SpecialCells(xlCellTypeConstants, xlNumbers).Count
    Worksheets("´Á¤¤²Î­p").Range("d4").Value = aa
    Worksheets("´Á¤¤²Î­p").Range("h4").Value = bb
    Worksheets("´Á¤¤²Î­p").Range("l4").Value = cc
    Worksheets("´Á¤¤²Î­p").Range("p4").Value = dd
    Worksheets("´Á¤¤²Î­p").Range("t4").Value = ee

skyutm

¯u¬O©êºp¡I§Ú¦Û¤v¦b®Ñ¤W§ä¨ìµª®×¤F¡C§Ú·Q§ï¥Îscrollarea¡]­­¨î²¾°Ê½d³ò»yªk¡^

skyutm

©êºp¡I¦³³Ò¨â¦ì¤SÅý¤p§Ì¤W¤F¤@½Ò¡C­ì¨Óµo²{¬O¦Û¤vªº°ÝÃD¡C°ÝÃD¦b³o¸Ì¡A¬°¤F«OÅ@¦¨ÁZ³æªº¼ÐÃD¦C¡A©Ò¥H§Ú¼g¤F¡]·íµM¬O¨â¦ì­×¥¿ªº¡^¤U¦C»yªk¡G
Option Explicit
Sub Worksheet_Activate()
    With Worksheets("´Á¤¤¦¨ÁZ")
        .Activate
        .Range("b1").Select
        .Range("A3:j3").Select
        .Range("a5", .cells(5, 1).End(xlDown)).Select
        Selection.Locked = True
        Selection.FormulaHidden = True
        .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=6323
    End With
End Sub
¦ý¬O¦¨ÁZ³æªº¸¹½X¦C¥²¶·¸òÀH°ò¥»¸ê®ÆÄæ¡]´N¬O¶ñ¯Z¯Å¡B¤H¼Æ´N·|¦Û°Ê¥X²{©Ò¦³®y¸¹¡^³o®É»yªk´N·|¥X²{¿ù»~¡A¦]¬°µLªk§ó°Ê¡C©Ò¥H§Ú¤S¼g¤F
Sub Worksheet_Deactivate()
    Worksheets("´Á¤¤¦¨ÁZ").Unprotect
    End Sub
¦ý¬O³o¼Ëªº¸Ü¡A¤@Â÷¶}³o­Ó¤u§@ªí¡A°T®§¦C´N·|°Ý§Ú¸Ñ¶}ªº±K½X¡A©Ò¥H¡G1.¸Ó«ç»ò¸Ñ¨M©O¡H2.©Î¬O¦³¨ä¥L¥i¥H«OÅ@¼ÐÃD¦Cªº»yªk¶Ü¡H¡]¤S­n³Â·Ð¤F¡^

skyutm

³o¬O§Ú©Ò«ô³X¹L¡A¦^À³³t«×³Ì§Ö¡AºA«×³Ì¼ö¤ßªºµ{¦¡½×¾Â¡A¡]¥u¦]§Ú¬O¥Î¼·¸¹¤Wºô56k¡A©Ò¥H¤[¤[¤~¯à¤W¨Ó¤@¦¸¡^

GBKEE

¦^´_ 2# kevin681024
¤Ö¤@ªº .
With Union(.Range("A1:V2"), .Range("a3", .Cells(3, 1).End(xlDown)))

¦^´_ 1# skyutm
Selection ÄÝ©Ê   ¦pªG¬O Application ª«¥ó¡A«h¶Ç¦^²{¥Îµøµ¡¤¤ªº¿ï©wª«¥ó¡A¦pªG¬O Windows ª«¥ó¡A«h¶Ç¦^«ü©wµøµ¡¡C

1. Option Explicit
   
2. Sub Worksheet_Activate()
   
3.     'Worksheet_Activate: ¿ï¾Ü **¦¹¤u§@ªí**ªº©ÒIJ°Êªº¨Æ¥óµ{§Ç
   
4.     '---¦p¦³¶}±Ò2­Óµøµ¡-------------------------------------------------
   
5.     'MsgBox Windows(2).Selection.Address(, , , 1)  '²Ä¤G­Óµøµ¡ ¨Ï¥Î¤¤Àx¦s®æ
   
6.     '--------------------------------------------------------------------
   
7.     MsgBox Selection.Address(, , , 1)         '¨Ï¥Î¤¤µøµ¡ªº ¨Ï¥Î¤¤Àx¦s®æ
   
8.    
   
9.     'Worksheets("´Á¤¤µû¶q") ¬Oª«¥ó  ¨S¦³Selection ³oÄÝ©Ê
   
10.    
    
11.     With Worksheets("´Á¤¤µû¶q") '¦p¤£¬O**¦¹¤u§@ªí**
    
12.         
    
13.         .Activate
    
14.         'Activate: Âà¨ì"´Á¤¤µû¶q"  <--** ¦]¦³.Select ¤£µM·|¥X¿ù
    
15.         .Range("A1:v2").Select
    
16.         .Range("a3", .Cells(3, 1).End(xlDown)).Select
    
17.         '.Selection.Locked = True
    
18.         '.Selection.FormulaHidden = True
    
19.         Selection.Locked = True
    
20.         Selection.FormulaHidden = True
    
21.         .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    
22.         'Protect: ¬O¤u§@ªíªº¤èªk «e­±¥[¤W . µL¶·«ü©w ActiveSheet
    
23.     End With
    
24. End Sub

½Æ»s¥N½X

kevin681024

¦^´_ 1# skyutm
¸Õ¸Õ¬Ý³o¼Ë©O¡H

1. Sub Worksheet_Activate()
   
2. With Worksheets("´Á¤¤µû¶q")
   
3.     With Union(.Range("A1:V2"), .Range("a3", Cells(3, 1).End(xlDown)))
   
4.         .Locked = True
   
5.         .FormulaHidden = True
   
6.     End With
   
7.     .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
   
8. End With
   
9. End Sub

½Æ»s¥N½X