[µ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

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

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

skyutm

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

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

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

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

GBKEE

¦^´_ 1# skyutm
SpecialCells(xlCellTypeConstants, xlNumbers)  ¨S¦³¶Ç¦^«ü©wªºÀx¦s®æ·|¦³¿ù»~
¤u§@ªí¨ç¼Æ Count  :­pºâ§t¦³¼Æ¦rªºÀx¦s®æ¼Æ¶q
¤u§@ªí¨ç¼Æ CountA :­pºâ¤£¬OªÅ¥ÕªºÀx¦s®æ¼Æ¶q

1. Sub Ex()
   
2. Dim chall, aa
   
3. chall = Worksheets("°ò¥»³]©w").Range("d2").Value
   
4. With Worksheets("´Á¤¤µû¶q")
   
5. .Range("c2").Value = "°ê»yx" & chall
   
6. aa = Application.Count(.Range("c3", .Range("c3").End(xlDown)))
   
7. End With
   
8. Worksheets("´Á¤¤²Î­p").Range("d4").Value = aa
   
9. End Sub

½Æ»s¥N½X

skyutm

¦^´_ 8# GBKEE
­ì¨Óª©¥D¤]¬O¦­°_ªº³¾¨à¡A¦A¦¸·PÁ¡C¤£¹L¤p§ÌÁÙ¬O¦³¨ÇºÃ°Ý·Q´£¥X¡G1.dim chall,aa ³o¨à¬O¤£¬O¬Ù²¤¤F¤°»ò¡H®Ñ¤W»¡­n©w¸qÅܼÆ2.application«á­±©Ò¥[ªº¨ç¼Æ¬O¤£¬Oexcel¤u§@ªí¨Ï¥Îªº¨ç¼Æ¡H

GBKEE

¦^´_ 9# skyutm
Dim  ÅܼƠ As  «¬ºA(©Î ª«¥ó)   (¨S¦³«ü©ú´N¬O  Variants«¬ºA)
¨Ã«D©Ò¦³¤u§@ªí¨ç¼Æ¬Ò¥i¥Î©óVBA
¹Ï¤¤¥i¿ï¥ÎV BA¥i¥Î¤§¤u§@ªí¨ç¼Æ

¤U¸ü (7.49 KB)

2012-8-26 07:54

¤W¹Ï ¶·¦³¦¹³]©w

¤U¸ü (46.23 KB)

2012-8-26 07:55