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

[µo°Ý] ¸T¤î ÂùÀ»Àx¦s®æÃä¬É

[µo°Ý] ¸T¤î ÂùÀ»Àx¦s®æÃä¬É

¸T¤î ÂùÀ»Àx¦s®æÃä¬É
border double click.zip (17.39 KB)

§Ú¦³¤@­Ó EXCLE µe­±¤Ó¹L±K¶°¡A¸g±`¤£¤p¤ß ÂùÀ»¨ìÀx¦s®æªºÃä¬É´N²¾¨ìµe­±³Ì¤U­±¡A¤Q¤À§xÂZ

§Ú¤Wºô´M§ä¡A³Ì«á§ä¨ìªº¤è¦¡´N¬O±N TOOL->OPTION->EDIT->Allow cell drag and drop(¨Ï¥ÎÀx¦s®æ©ì©ñ¥\¯à¡^ ¨ú®ø


¤§«á§Ú§ä¨ì§ó¶i¤@¨Bªº³]©w¤è¦¡¡AÅý¯S©w°Ï°ìªº Ãä¬ÉÂùÀ»²¾°Ê¥¢®Ä

Dim SaveDragAndDrop As Variant 'For persistence, is declared at module level

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     'NOTE: This event fires first, then the Worksheet_BeforeDoubleClick event.
     '
     'WARNING: Setting a breakpoint in this event will, in effect, cancel any BeforeDoubleClick event, so you can't
     '         single-step through the whole sequence!
     
     'To prevent unwanted jumping to the "End" of a data-set if the user accidentally double-clicks onto the cell
     'border (which is an effect of CellDragAndDrop), disable that functionality while in the range where that
     'behavior is a problem.
     
    If Not Intersect(Target, Range("MyProtectedRange")) Is Nothing Then
         
        If IsEmpty(SaveDragAndDrop) Then
            SaveDragAndDrop = Application.CellDragAndDrop
            Application.CellDragAndDrop = False
        End If
    Else
         
        If Not IsEmpty(SaveDragAndDrop) Then
            Application.CellDragAndDrop = SaveDragAndDrop
            SaveDragAndDrop = Empty
        End If
    End If
End Sub





¦WºÙ MyProtectedRange =Sheet1!$C$4:$F$16

Book_ok.xls ÂI¿ï¶À¦â³¡¥÷¡A ÂùÀ»¨ìÀx¦s®æªºÃä¬É ¥¢®Ä

ÂI¿ïªÅ¥ÕÀx¦s®æ¡AÂùÀ»Ãä¬É²¾°Êªº¥\¯à ¦³®Ä

¦ý¬O³o­Óµ{¦¡¦³BUG ¡A·í§ÚÂI¶À¦â«á(¥¢®Ä)¡A¦A²¾¨ì SHEET2 ´N³q³q¥¢®Ä¤F¡A¥²»Ý²¾¦^SHEET1¡A¦AÂI¿ï¥Õ¦âÀx¦s¥i¤~·|¦A¥Í®Ä¡C

©Ò¥H§Ú±N SelectionChange ²¾¨ì ThisWorkBook Åܦ¨ SheetSelectionChange

¦ý¬O Intersect «o¦³°ÝÃD¡A½Ð°Ý¨º¦³°ÝÃD¡HÀ³¸Ó«ç»ò­×§ï¤~¯à¥¿½T
¤½¶}¸gÅç¡A³Ð³y´¼¼z¡]§Ú¤S¤£¾a³o¹L¬¡¡^

[¤À¨É]Excel¦­¨Æ¥ý¼g¦n¤§µ{¦¡½X

¡@¡@1¡B¦b¤u¨ãÄæ¥k°¼ªºªÅ¥Õ³BÂùÀ»¡A§Ö³t¥´¶}¡§¦Û©w¡¨¹ï¸Ü¤è¶ô¡C

¡@¡@2¡BÂùÀ»[Àx¦s®æ]Á`¦@6³B
      A.ÂùÀ»Àx¦s®æ¤¤¶¡:¤J½s¿èª¬ºA¡C
      B.ÂùÀ»Àx¦s®æ¥~®Ø½u:¤W¤U¥ª¥k¶],»{¦r§@§PÂ_
      C.ÂùÀ»Àx¦s®æ¥k¤U¨¤¶ñº¡±±ÂI:»\³¹,¬Û¾F¦C¤¤¥²¶·¦³³sÄòªº¸ê®Æ

¡@¡@3¡B¦b¡§®æ¦¡¨ê¡¨«ö¶s¤WÂùÀ»¡A®æ¦¡¨ê¥i¥H¤Ï´_¦h¦¸¨Ï¥Î¡C
¡@¡@   [¯S§O´£¥Ü]¡@¦A¦¸³æÀ»¡§®æ¦¡¨ê¡¨«ö¶s¡A©ÎªÌ«öEscÁä¡A¥i¥H¨ú®ø¡§®æ¦¡¨ê¡¨¥\¯à¡C

¡@¡@4¡B¦b¼ÐÃDÄæ¤WÂùÀ»¡AExcelµøµ¡¥Ñ³Ì¤j¤Æ¡]­ì©lª¬ºA¡^ÁÙ­ì¨ì­ì©lª¬ºA¡]³Ì¤j¤Æ¡^¤j¤p¡C

¡@¡@5¡B¿ï¤¤¬Y­Ó³æ®æ¨Ã±N·Æ¹«²¾¦Ü¸Ó¸Ó³æ®æ¥k¤U¨¤¦¨²Ó¤Q¦r½uª¬®É¡AÂùÀ»·Æ¹«¡A
¡@¡@   [¯S§O´£¥Ü]¡@­n¹ê²{¦¹¶ñ¥R¡A¸Ó³æ®æ¥ª°¼¡]©Î¥k°¼¡^¬Û¾F¦C¤¤¥²¶·¦³³sÄòªº¸ê®Æ¡C
            //¾Ç°Ý¦b¦p¦ó¿ïRange,¸ê®Æ«¬ºA¬Û¦P©Î¤£¦P·|²£¥Í¤£¦P¤§µ²ªG

¡@¡@6¡B¦pªG¤u¨ã±ø¯B°Ê¦b¤u§@ªí°Ï¤¤¡A§Ú­Ì¦b¤u¨ã±øªº¼ÐÃDÄæ¤WÂùÀ»¡A¸Ó¤u¨ã±øªð¦^¤u§@ªí°Ï¤W³¡©Î¤U³¡¦ì¸m¡C

¡@¡@7¡B¦bµøµ¡¥ª¤W¨¤Excel¼Ð»x¤WÂùÀ»¡A«h°h¥XExcel¡]¦pªG·í«e¤åÀɨS¦³«O¦s¡A¨t²Î·|´£¥Ü«O¦s¡^¡C

¡@¡@8¡B¦b¤u§@ªí¦WºÙ¡]Sheet1µ¥¡^¤WÂùÀ»¡A§Y¥i¹ï¤u§@ªí¦WºÙ¶i¦æ¡§­«©R¦W¡¨¡C

¡@¡@9¡B±N·Æ¹«²¾¦Ü««ª½±²¶b¤WºÝ»P½s¿è°Ï¥æ¬É³B¦¨Âù¦V©ì©Ô½bÀYª¬®É¡AÂùÀ»·Æ¹«¡A§Y¥i±N½s¿è°Ï©î¤À爲¤W¡B¤U¨â­Óµ¡¤f¡C
       ¦¹®É¡A¦pªG±N·Æ¹«²¾¦Ü¨â­Óµøµ¡¤À¬É½u¤WÂùÀ»¡A§Y¥iºM¾Pµ¡¤fªº©î¤À¡C

¡@¡@10¡B±N·Æ¹«²¾¦Ü¤ô¥­±²¶b¥kºÝ¦¨Âù¦V©ì©Ô½bÀYª¬®É¡AÂùÀ»·Æ¹«¡A§Y¥i±N½s¿è°Ï©î¤À爲¥ª¡B¥k¨â­Óµ¡¤f¡C
        ¦¹®É¡A¦pªG±N·Æ¹«²¾¦Ü¨â­Óµøµ¡¤À¬É½u¤WÂùÀ»¡A§Y¥iºM¾Pµ¡¤fªº©î¤À¡C

¡@¡@11¡B±N·Æ¹«²¾¦Ü¦C¼Ð»P¦C¼Ð¥æ¬É³B¦¨Âù¦V©ì©Ô½bÀYª¬®É¡AÂùÀ»·Æ¹«¡A§Y¥i§Ö³t±N¥ª°¼1¦C³]¸m爲¡§³Ì¾A¦Xªº¦C¼e¡¨¡C
        ¦pªG¿ï¤¤¦h¦C¡AµM«á°õ¦æ¦¹¾Þ§@¡A§Y¥i±N¿ï¤¤ªº¦h¦C³]¸m爲¡§³Ì¾A¦Xªº¦C¼e¡¨¡C

¡@¡@12¡B±N·Æ¹«²¾¦Ü¦æ¼Ð»P¦æ¼Ð¥æ¬É³B¦¨Âù¦V©ì©Ô½bÀYª¬®É¡AÂùÀ»·Æ¹«¡A§Y¥i§Ö³t±N¤W­±1¦æ³]¸m爲¡§³Ì¾A¦Xªº¦æ°ª¡¨¡C
        ¦pªG¿ï¤¤¦h¦æ¡AµM«á°õ¦æ¦¹¾Þ§@¡A§Y¥i±N¿ï¤¤ªº¦h¦æ³]¸m爲¡§³Ì¾A¦Xªº¦æ°ª¡¨¡C

¡@¡@13¡B¦b¥\¯àªí¤WÂùÀ»¡A§Y¥i±N¥\¯àªí¤¤©Ò¦³ªº¥\¯àªí¶µ¡]¥]¬A¤£±`ªº¥\¯àªí¶µ¡^¥þ³¡®i¶}¡C
        [¯S§O´£¥Ü]ê¤âê¸}¤§·s¥\¯à
        ¥i¥H³]©w¥þ³¡®i¶},¦b¤u¨ãÄæ¥k°¼ªºªÅ¥Õ³BÂùÀ»¡A§Ö³t¥´¶}¡§¦Û©w¸q¡¨¹ï¸Ü¤è¶ô¡C
        ¿ï¶µ>>¨ú®ø­º¥ýÅã¥Ü³Ìªñ¨Ï¥Îªº¥\¯àªí

¡@¡@14¡BÂùÀ»¼Ï¯Ã¤ÀªRªí¤¤ªº¸ê®Æ, ¥i¦b·sªº¤u§@ªí¤¤¦C¥X¸Ó¸ê®Æªº©ú²Ó
        ÂùÀ»¼Ï¯Ã¤ÀªRªí¤¤Äæ¦ì¦WºÙ:¥s¥X¸ÓÄæ¦ìÄݩʪí
        ÂùÀ»¼Ï¯Ã¤ÀªRªí¤¤ªÅ¥ÕÀx¦s®æ:¸s²ÕÁY©ñ¥\¯à

        15¡BÂùÀ»[¦Û°Ê¥[Á`]«ö¶s¦Û°Ê²£¥Í¤½¦¡SUM()
        ¦b¿z¿ïª¬ºA¤U,ÂùÀ»[¦Û°Ê¥[Á`]«ö¶s¦Û°Ê²£¥Í¤½¦¡SUBTOTAL(9,)

        16¡Bø¹Ï¼hª«¥ó,ÂùÀ»¥~®Ø:¥s¥X¸Óª«¥óÄݩʪí

TOP

Excel¬Oª«¥ó¾É¦V¤§µ{¦¡³]­pÀô¹Ò

COM:²Õ¦X¤¸¥ó¤Æ¤§ª«¥ó¼Ò¯ª

OLEµ²ºc***¤T¦X¤@µ²ºc:[¥]¸Ë][´O¤J][³sµ²]***µ{¦¡½X¤À[ÄÝ©Ê][¨Æ¥ó][¤èªk]

ª½±µ¨Ï¥Î:¤£¥²¥h¼g¤S¯ä¤Sªø¤§ VBAµ{¦¡½X-¤Î¨ç¼Æ

*********************************************************

[»{ÃѤu§@ªí]

1.CTRL+¦V¥kÁä+¦V¥kÁä>>¦ÜIV65536³æ¤¸®æ>>Áä¤J1>>«öENTER
//'µ¹¦r§@§PÂ_±ø¥ó


ÂùÀ»¿ï¨ú³æ¤¸®æ¥~®Ø: ¹w³]­È¦^²Ä¤@Äæ©Î²Ä¤@¦C
¦p¦ó©¹¥k¤Î©¹¤U¶]? ¸Ó¤è¦V¥²»Ý¦³¦r¤~·|¶]
//'»{¦r§@§PÂ_±ø¥ó

Excel¤º«Ø¤§µ{¦¡¼Ò²Õ,±µ¾aÂùÀ»[¨Æ¥ó]¥hIJµo¸Ó¥\¯à

TOP

¦^´_ 3# eigen


    ¦³¨S¦³°ª¤â¯à«üÂI¤@¤U¡A§Ú¥Î Application.CellDragAndDrop = False ' "½d³ò¤º"

¤w¸g¯à enable /disable ÂùÀ»Ãä¬Éªº¥\¯à

¦ý¬OApplication.CellDragAndDrop ³o­Ó¥\¯à¡A·|Åý Àx¦s®æµLªk³Q select / copy /paste ¡A¹ê¦b¬O¤Ó³Â·Ð¤F¡A¯à¤£¯à½Ð°ª¤â«üÂI¤@¤U¡H

¦p¦ó±N¥Ø«eselect/copy ªº¸ê®Æ³Æ¥÷¤U¨Ó¡H ¥H«K·í Application.CellDragAndDrop  ¤Á´«®É¡A¦AÁÙ­ì¦ì¸m¦^¨Ó
¤½¶}¸gÅç¡A³Ð³y´¼¼z¡]§Ú¤S¤£¾a³o¹L¬¡¡^

TOP

¦^´_ 2# eigen

¦³¿³½ìªº¡A¥i¥H¨ì https://www.mobile01.com/topicdetail.php?f=511&t=5120593&p=1#64021269

¬Ý¨ì¬ÛÃöªº°Q½×¡A§Ú¤]¦³¾ã²z§¹ªº½d¨Ò~~
¤½¶}¸gÅç¡A³Ð³y´¼¼z¡]§Ú¤S¤£¾a³o¹L¬¡¡^

TOP

¦Û°Ý¦Ûµª¡G
¦b ThisWorkBook  ¥[¤J
Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Application.CellDragAndDrop = True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Dim ProtectedRange As Range
Set ProtectedRange = Worksheets("Sheet1").Range("c4:f16")


If Sh.Name = "Sheet1" Then

        If Application.Intersect(Target, ProtectedRange) Is Nothing Then
                Application.CellDragAndDrop = True
        Else
                Application.CellDragAndDrop = False
        End If
Else
        
        Application.CellDragAndDrop = True
End If


End Sub


°ß¤@Åý§Ú¤£¸Ñªº¬O
Application.Intersect(Target, ProtectedRange)  ok

¬°¤°»ò
Application.Intersect(Target, MyProtectedRange)
MyProtectedRange =¦WºÙ = =Sheet1!$C$4F$16 ¬°¤°»ò´N¤£¦æ¡H
¤½¶}¸gÅç¡A³Ð³y´¼¼z¡]§Ú¤S¤£¾a³o¹L¬¡¡^

TOP

        ÀR«ä¦Û¦b : ¤H­n¦Û·R¡A¤~¯à·R´¶¤Ñ¤Uªº¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD