[µo°Ý] Workbook_SheetChange ºÃ°Ý

swatt

¦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¶Ü?

¤U¸ü (81.96 KB)

2016-10-6 14:57

1. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   
2. 
   
3. If Target = [h2] Or Target = [i2] Then
   
4.   
   
5. 
   
6.     If (Not IsNumeric(Target.Value)) Or IsEmpty(Target.Value) Then
   
7.    
   
8.         MsgBox "Must be numeric value!"
   
9.         Application.EnableEvents = False
   
10.         Target.Value = ""  'this will trigger Target again
    
11.         [j2].Value = ""    'clear the cell of yield
    
12.         Application.EnableEvents = True
    
13.         Exit Sub
    
14.    
    
15.     End If
    
16. End If
    
17. 
    
18. End Sub

½Æ»s¥N½X

swatt

¦^´_ 9# c_c_lai


¤@­Ó°ÝÃD¯u¦³¤£¦P¸Ñªk¡A§Ú¦Û¤v¹ïVBA¤S¦h¤@ÂI¤F¸Ñ¤F¡A¦A¦¸·PÁ¨â¦ì¤j¤jÅo~~

swatt

¦^´_ 8# GBKEE

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

c_c_lai

¦^´_ 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

GBKEE

¦^´_ 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

swatt

¦^´_ 6# c_c_lai


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

c_c_lai

¦^´_ 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

swatt

¦^´_ 4# c_c_lai

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

c_c_lai

¦^´_ 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

swatt

¦^´_ 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?