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

[µo°Ý] Workbook_SheetChange ºÃ°Ý

[µo°Ý] Workbook_SheetChange ºÃ°Ý

¦U¦ì¤j¤j,
¤@­Ó²³æªº IF §PÂ_¦¡°ÝÃD¡A«o¤£ª¾°ÝÃD¥X¦b­þ¸Ì?
µ{¦¡²Ä¤@­Ó IF¥ý§PÂ_¬O¤£¬O[h2] or [i2] ¦³ÅÜ°Ê¡A¦³ªº¸Ü¦A¶i¤J²Ä2­Ó IF§PÂ_¬O§_¬°¼Æ­È or ªÅ¥Õ¡A
¥¿±`±¡ªp¤U¶]°_¨Ó¬O¨S°ÝÃD¡A¦ý¦³¤@­Ó¯S§Oª¬ªp:

¦b«D[h2] or [i2] ªºcell «ö¤UdeleteÁä¡AEX: [b6]«ö¤UdeleteÁä(²MªÅ¸ê®Æ)¡A
³ºµM·|¶]¨ì²Ä2­ÓIF§PÂ_¦¡¡A¦Ó¥X²{ MsgBox "Must be numeric value!"

¦³¤j¤j¥i¥H¸Ñµª¤@¤U¶Ü?

abnormal.jpg
2016-10-6 14:57
  1. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

  2. If Target = [h2] Or Target = [i2] Then
  3.   

  4.     If (Not IsNumeric(Target.Value)) Or IsEmpty(Target.Value) Then
  5.    
  6.         MsgBox "Must be numeric value!"
  7.         Application.EnableEvents = False
  8.         Target.Value = ""  'this will trigger Target again
  9.         [j2].Value = ""    'clear the cell of yield
  10.         Application.EnableEvents = True
  11.         Exit Sub
  12.    
  13.     End If
  14. End If

  15. End Sub
½Æ»s¥N½X
abnormal.jpg

¦^´_ 1# swatt
  1. If Target = [h2] Or Target = [i2] Then
½Æ»s¥N½X
§ï¬°
  1. If Target.Address = "$H$2" Or Target.Address = "$I$2" Then
½Æ»s¥N½X
§Y¥i¡C

TOP

¦^´_ 2# c_c_lai


·PÁ c_c_lai  ¤j¤j¡A§ï§¹¤§«á½T¹ê¤£·|¦Aµo¥Í¡A
½Ð°Ý¬°³o»ò²Ä¤@ºØ¼gªk·|¥X¿ù?
¥H«á¦pªG­n°Ñ·Ócell¡A¬O§_³£­n¥Îaddress ¡A¤£­n¥Îrange?

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2016-10-6 16:43 ½s¿è

¦^´_ 3# swatt
Target.Address = "$H$2" ¬O©ú½Tªº«ü¥X [H2] ªº¦ì§}¡C
¥ç§Y¦pªG¦³¥ô¦óÄ欰­È¦³²§°Ê¡A §Y¦æIJµo Workbook_Change()¡A
±µµÛ§PÂ_¸ÓIJ°ÊÄæ¦ì¬O§_¬°   [H2] ©Î [I2] Äæ¦ì­Èªº²§°Ê¡C
±ø¥ó²Å¦X¡A§Y¦æ«ö·Ó²Å¦X±ø¥óÄ~Äò°õ¦æ¡F§_«h¸õÂ÷¡C

TOP

¦^´_ 4# c_c_lai

ÁÂÁ¤j¤j¡A§Ú¬O¤F¸Ñaddress¥i±o¨ì©ú½Tªº¦ì§}¡A
¨º   If Target = [h2] ·|¥¢ÆF¡A¥i¯à¬O¦ó­ì¦]??

TOP

¦^´_ 5# swatt
  1. If Target = [h2] Or Target = [i2] Then
½Æ»s¥N½X
¥u­n¤º®eµ¥©ó [h2] ­È ©ÎªÌ¬O [i2] ­ÈªºÄæ¦ì²§°Ê§¡Äݤ§¡C
Ä´¦p¡G [h2]  ¬° ""¡F c6 = "ABC"¡A ·í  c6 «ö DEL ²M°£®É¡A«h  c6  ­È·|µ¥©ó [h2] ­È¡A
©Ò¥H±ø¥ó¦¨¥ß¡C¥H¦¹Ãþ±À¡A°²³] [h2]  = "ABC"¡A ·í¥ô¦óÄæ¦ì­Èµ¥©ó  "ABC"¡A
«h±ø¥ó¦¨¥ß¡C

TOP

¦^´_ 6# c_c_lai


²×©ó¤F¸Ñ­þ¸Ì¿ù¤F¡A   ¥Îaddress ¨Ó¤ñ¹ï¦ì§}¤~¬O¤Wµ¦¡A¦A¦¸ÁÂÁÂc_c_lai¤j¤j¸Ô²Óªº¸Ñ»¡.

TOP

¦^´_ 7# swatt
  1. Option Explicit
  2. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  3. Dim Rng As Range
  4.     Set Rng = Range("H2:I2")
  5.     If Not Intersect(Target, Rng) Is Nothing Then
  6.     '*******************************************************************
  7.     'Intersect ¤èªk   ¶Ç¦^ Range ª«¥ó¡A¦¹ª«¥ó¥Nªí¨â­Ó©Î¦h­Ó½d³ò­«Å|ªº¯x§Î½d³ò
  8.     '*******************************************************************
  9.     'If Target = [h2] Or Target = [i2] Then
  10.         If (Not IsNumeric(Target.Value)) Or IsEmpty(Target.Value) Then
  11.             MsgBox "Must be numeric value!"
  12.             Application.EnableEvents = False
  13.             Target.Value = ""  'this will trigger Target again
  14.             [j2].Value = ""    'clear the cell of yield
  15.             Application.EnableEvents = True
  16.         'Exit Sub
  17.         End If
  18.     End If
  19. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 7# swatt
§A¤]¥i¥HÀ³¥Î GBKEE ª©¤jªº½d¨Ò¨Ó©µ¦ù§Aªº»Ý¨D¡G
  1.     Set Rng = Range("H2:I2")
  2.     If Not Intersect(Target, Rng) Is Nothing Then
  3.         MsgBox "±ø¥ó²Å¦X¡I"
  4.     Else
  5.         MsgBox "±ø¥ó¤£¦¨¥ß¡I"
  6.     End If
½Æ»s¥N½X

TOP

¦^´_ 8# GBKEE

·PÁÂGBKEE¤j¤j´£¨Ñ¥t¤@­Ó·Qªk¡A§Q¥Îrangeªº¥æ¶°¨Ó§PÂ_¡A¤S¾Ç¨ì¤@½Ò°Õ~

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡¦pÆp¥Û¡j®É¶¡¹ï¤@­Ó¦³´¼¼zªº¤H¦Ó¨¥¡A´N¦pÆp¥Û¯ë¬Ã¶Q¡F¦ý¹ï·M¤H¨Ó»¡¡A«o¹³¬O¤@§âªd¤g¡A¤@ÂI»ù­È¤]¨S¦³¡C
ªð¦^¦Cªí ¤W¤@¥DÃD