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

[µo°Ý] VBA ½s¼g½Æ»s¶i¤J¦º´`Àô

[µo°Ý] VBA ½s¼g½Æ»s¶i¤J¦º´`Àô

¥»©«³Ì«á¥Ñ dnadark ©ó 2015-4-27 14:57 ½s¿è

±z¦n¡G

ªþ¥ó°£AÄ椧¥~³£±NÀx¦s®æÂê©w¶i¦æ«OÅ@
°ßAÄæ­È¤j©ó©Î¤p©óBÄæ®É¡A·|±NAÄæÀx¦s®æªº¸ê®Æ½Æ»s¨ìCÄæ
¨ÃÂê¦íAÄæ¸ÓÀx¦s®æ¡A¦A«OÅ@¤u§@ªí¡A
¦ý¬O½Æ»s®É¤£Â_³´¤J¦º´`Àô¡A½Ð°Ý¦³¨S¦³¨ä¥Lªº¼gªk¥i¥H§ïµ½¡H

If Cells(Target.Row, 1) <> "" And Cells(Target.Row, 1) > Cells(Target.Row, 2) * 2 Or Cells(Target.Row, 1) <> "" And Cells(Target.Row, 1) < Cells(Target.Row, 2) * 0.5 Then
ActiveSheet.Unprotect
Cells(Target.Row, 3) = Cells(Target.Row, 1)---->¥d¦í
Cells(Target.Row, 1).Locked = True
  ActiveSheet.Protect
End If

cats.jpg (27.42 KB)

cats.jpg

¦^´_ 1# dnadark
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     On Error GoTo ERR_HANDLE
  3.     If Cells(Target.Row, 1) <> "" And Cells(Target.Row, 1) > Cells(Target.Row, 2) * 2 Or Cells(Target.Row, 1) <> "" And Cells(Target.Row, 1) < Cells(Target.Row, 2) * 0.5 Then
  4.         ActiveSheet.Unprotect
  5.         Application.EnableEvents = False
  6.         Cells(Target.Row, 3) = Cells(Target.Row, 1)
  7.         Cells(Target.Row, 1).Locked = True
  8.         ActiveSheet.Protect
  9.     End If
  10.    
  11. BEFORE_EXIT:
  12.     Application.EnableEvents = True
  13.     Exit Sub

  14. ERR_HANDLE:
  15.     MsgBox Err.Description, vbCritical, "ERROR:" & Err.Number
  16.     Resume BEFORE_EXIT
  17. End Sub
½Æ»s¥N½X
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

±z¦n¡G
        ÁÂÁ¡I¤w¸g¸Õ¦¨¥\¤F¡A¦ý¬O¸Ó¥¨¶°§¹¤F¤§«á¨ä¹ê«á­±ÁÙ¦³¼g¨ä¥L¥¨¶°¡A
¦pªþ¥ó¡A­è­èªº¬O·í¤£²Å¦X®Éªºª¬ªp¡A¦ý¬O²Å¦X±ø¥ó®É·|±N²Å¦Xªº¦Cªº¸ê®ÆÂê¦í
¨Ã±N¨ä¥Lªº¦C¤]¥þÂê¦í¡A¥u¶}©ñ¤U¤@¦C¨Ñ¼g¤J
¦ý¬O¥[¤U±z©Ò´£¨Ñªº¸ê®Æ«á¡A«áÄò³£µL®Ä¡A½Ð°Ý­n«ç»ò±µ©O¡H

       If Cells(Target.Row, 1) <> "" And Cells(Target.Row, 3) <> "" Then
    ActiveSheet.Unprotect
    Range(Cells(Target.Row, 1), Cells(Target.Row, 3)).Locked = True
    Range(Cells(Target.Row + 2, 1), Cells(1048576, 3)).Locked = True
    Range(Cells(Target.Row + 1, 1), Cells(Target.Row + 1, 3)).Locked = False
     ActiveSheet.Protect
    Cells(Target.Row + 1, 1).Select
    end if

cats1.jpg (53.78 KB)

cats1.jpg

TOP

¦^´_ 3# dnadark
´¡¤J¦b2# µ{¦¡½Xªº²Ä10¦æ¬Ý¬Ý
¤£µM´N¤W¶ÇÀÉ®×
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

±z¦n¡G¸Õ¹L¤j¦¨¥\¡I¡I«D±`ÁÂÁ±z¡I

TOP

        ÀR«ä¦Û¦b : µoµÊ®ð¬Oµu¼ÈªºµoºÆ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD