[µo°Ý] ¨D°ª¤â¸Ñµª¨Ì±ø¥ó¿ë§O¦Û°Ê¿é¤JÀx¦s®æ

newlink

¤p§Ì¬°¨Ïºû­×¸ê®Æ«ØÀɧֳt¡B¥¿½T¡A»Ý¨D¥H¤U²[¼Æ³]­p
       A       B          C         D
1  «O©T  ª÷ÃB  ³ø»ù¤é  ¦P·N¤é
2
3

AÄ榳¤U©Ô¿ï³æ¡A¤À§O¬O¡G«O¤º¡B¤G­×¡B«O¥~¡B¤H¬°
·íA2¿ï¾Ü«O¤ºor¤G­×¡AB2  C2  D2·|¦Û°Ê¶ñ¤W -- ¡A¦]¬°¤£¯à¦³ª÷ÃB¡A¤]¤£·|³ø»ù
·íA2¿ï¾Ü«O¥~or¤H¬°¡AB2¦Û°Ê¶ñ¤W¡¨¶ñª÷ÃB¡¨°µ¬°¦¬¶O´£¿ô¡A
·íB2§ï¶ñ¤W³ø»ùª÷ÃB®É¡AC2¦Û°Ê¶ñ¤WTODAY¤é´Á

ªþ¤WÀɮסA½Ð°ª¤â«üÂI¡A·PÁ¡I

iceandy6150

¦^´_ 1# newlink

«¢«¢¡A§Ú¬Oµæ³¾¡A¼g¤F¶§¬Kª©ªº
¥i¬O­n«ç»òÅýµ{¦¡¯à¤@ª½°õ¦æ¡A§Ú¤£·|
¦pªG³]¤@­Ó«ö¶s¡A¨C«ö¤@¦¸¡A¥i¥H¨Ï¥Î¤@¦¸¡A¥u°w¹ï²Ä¤G¦C

­n«ç»òÀH®ÉÀHµÛªí®æ¿é¤J´N°¨¤W¸õ°Ê¡A§Ú¤£·|¡AÁÙ­n¦A­×§ï

Sub ex()

Dim i
i = Sheets("¤u§@ªí1").Cells(2, 1).Value
MsgBox (i)

If i = "" Then
Exit Sub

   ElseIf i = "«O¤º" Then

        Sheets("¤u§@ªí1").Cells(2, 2).Value = "--"
        Sheets("¤u§@ªí1").Cells(2, 3).Value = "--"
        Sheets("¤u§@ªí1").Cells(2, 4).Value = "--"
        
   ElseIf i = "¤G­×" Then

        Sheets("¤u§@ªí1").Cells(2, 2).Value = "--"
        Sheets("¤u§@ªí1").Cells(2, 3).Value = "--"
        Sheets("¤u§@ªí1").Cells(2, 4).Value = "--"
   
  
   ElseIf i = "«O¥~" Then

       If Sheets("¤u§@ªí1").Cells(2, 2).Value = "" Then
        Sheets("¤u§@ªí1").Cells(2, 2).Value = "¶ñª÷ÃB"
       End If
        
       If Sheets("¤u§@ªí1").Cells(2, 2).Value <> "" Then
        Sheets("¤u§@ªí1").Cells(2, 3).Value = "=Today()"
       End If
   
Else

       If Sheets("¤u§@ªí1").Cells(2, 2).Value = "" Then
        Sheets("¤u§@ªí1").Cells(2, 2).Value = "¶ñª÷ÃB"
       End If
        
       If Sheets("¤u§@ªí1").Cells(2, 2).Value <> "" Then
        Sheets("¤u§@ªí1").Cells(2, 3).Value = "=Today()"
       End If

End If

End Sub

GBKEE

¦^´_ 1# newlink
³o¤u§@ªí¼Ò²Õ¹w³]ªºÄ²°Êµ{¦¡½X

1. Option Explicit
   
2. Private Sub Worksheet_Change(ByVal Target As Range)
   
3.     'Change : ¤u§@ªí¸ê®Æ¦³ÅܰʮɩÒIJµoªºµ{§Ç
   
4.     Application.EnableEvents = False
   
5.     Select Case Target(1).Column
   
6.     Case 1
   
7.         '·íA2¿ï¾Ü«O¥~or¤H¬°¡AB2¦Û°Ê¶ñ¤W¡¨¶ñª÷ÃB¡¨°µ¬°´£¿ô¡A
   
8.         If Target(1) = "«O¥~" Or Target(1) = "¤H¬°" Then Cells(Target(1).Row, "B") = "¶ñª÷ÃB"
   
9.         
   
10.     Case 2
    
11.         '·íB2§ï¶ñ¤W³ø»ùª÷ÃB®É , C2¦Û°Ê¶ñ¤WTODAY¤é´Á
    
12.         If IsNumeric(Target(1)) And Target(1) > 0 Then
    
13.             Cells(Target(1).Row, "C") = Date
    
14.         Else            '¤£¬O¼Æ¦r¥B<0
    
15.             Target(1) = "¶ñª÷ÃB"
    
16.             Cells(Target(1).Row, "C") = ""
    
17.         End If
    
18.     End Select
    
19.     Application.EnableEvents = True
    
20. End Sub
    
21. 
    
22. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
23.     'SelectionChange :¤u§@ªíÀx¦s®æ ¦³²¾°Ê®É©ÒIJµoªºµ{§Ç
    
24.     If Not Application.Intersect(Range("a:a"), Target) Is Nothing Then  'Àx¦s®æ²¾°Ê¨ìAÄæ®É
    
25.         Range("a:a").Validation.Delete
    
26.         Target(1).Validation.Add xlValidateList, , , "«O¤º,¤G­×,«O¥~,¤H¬°"
    
27.         'Validation ª«¥ó¡A¸Óª«¥ó¥Nªí«ü©w½d³ò¤ºªº¸ê®ÆÅçÃÒ(¿é¤Jªº¸ê®Æ­n²Å¦X«ü©wªº¸ê®Æ)
    
28.         'AÄ榳¤U©Ô¿ï³æ¡A¤À§O¬O¡G«O¤º¡B¤G­×¡B«O¥~¡B¤H¬°
    
29.     End If
    
30. End Sub

½Æ»s¥N½X

yen956

¦^´_ 3# GBKEE
¨C¦¸¬Ý¨ì¤j¤jªº¤j§@, ³£¦³«Ü¤jªº¦¬Àò, ÁÂÁÂ!!
¦ý§Ú±N³o½g vba code ¶K¨ì·sªº workbook ªº sheet1 ¤º,
¦A¨ì sheet1 ¾Þ§@, ¦ü¥G¨S¦³¤ÏÀ³, ¨º­Ó¨BÆJº|±¼¤F

GBKEE

¦^´_ 4# yen956
¤£­n¶K¦b¤@¯ë¼Ò²Õ

1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
2.                 '***SelectionChange :¤u§@ªíÀx¦s®æ ¦³²¾°Ê®É©ÒIJµoªºµ{§Ç ****
   
3.                 '¦³²¾°Ê¤~¦³Ä²µo½Ð->,¥ý²¾°ÊÀx¦s®æ¦bAÄ椧¥~,¦A²¾°ÊÀx¦s®æ¨ìAÄ椺¬Ý¬Ý

½Æ»s¥N½X

yen956

¦^´_ 5# GBKEE
·PÁÂGBKEE¤j¤jªº¦^ÂÐ,
¦ý¬O§Ú´N¬O¶K¦b sheet1 ªºµ{¦¡°Ï, ¦ý¤£ºÞ¥Î, ¤£ª¾­þ¸Ì¦³°ÝÃD.
(§Ú¥u¬O»¡¼Ò²Õ¤]¸Õ¹L)
Àɮצp¤W:

yen956

¦^´_ 5# GBKEE
¸U¤À©êºp£®, ­è­è¤S¸Õ¤F¤@¤U, ¤w¸g¯à·Ó§Aªº³]­p°õ¦æ, ¸U¤À·PÁÂ,
(³Ìªñ¹q¸£©Ç©Çªº, ­è­è¤~­«·sÁÙ­ì, ¹ê¦b©êºp)

Hsieh

¦^´_ 1# newlink

¤U¸ü (61.61 KB)

2014-2-9 19:25

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. With Target(1)
   
3.   Select Case .Column
   
4.   Case 1
   
5.   .Offset(, 1).Resize(, 3) = IIf(.Value = "«O¤º" Or .Value = "¤G­×", "--", "¶ñª÷ÃB")
   
6.   Case 2
   
7.   .Offset(, 1).Resize(, 2) = IIf(IsNumeric(.Value) And .Value <> "", Date, "")
   
8.   End Select
   
9. End With
   
10. End Sub
    
11. 
    
12. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
13. If Target(1).Column = 1 Then '¿ï¨úAÄæ
    
14. With Target(1).Validation '³]©wÅçÃÒ²M³æ
    
15.   .Delete
    
16.    .Add xlValidateList, , , "«O¤º,¤G­×,«O¥~,¤H¬°"
    
17. End With
    
18. End If
    
19. End Sub

½Æ»s¥N½X

yen956

¦^´_ 5# GBKEE
                   ¡i '***SelectionChange :¤u§@ªíÀx¦s®æ ¦³²¾°Ê®É©ÒIJµoªºµ{§Ç ****
                    '¦³²¾°Ê¤~¦³Ä²µo½Ð->,¥ý²¾°ÊÀx¦s®æ¦bAÄ椧¥~,¦A²¾°ÊÀx¦s®æ¨ìAÄ椺¬Ý¬Ý¡j
ÁÂÁ¦A¤T»¡©ú, ¦¬¨ì.
¦A¦¸½Ð°Ý¡G
    ¡iPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)¡j
©Ò¥H Worksheet_SelectionChange«á©ÒIJµoªº²Ä¤@­ÓRange´N¬OTarget(1)¬O¤£¬O?

GBKEE

¦^´_ 9# yen956

1. Option Explicit
   
2. Sub Ex()  '°õ¦æ¦¹µ{¦¡·|IJ°Ê Worksheet_SelectionChange(ByVal Target As Range)
   
3.     [D5:E6].Select
   
4. End Sub
   
5. Private Sub Worksheet_SelectionChange(ByVal Target As Range)   'Target-> [D5:D6]
   
6.     Dim i
   
7.     MsgBox Target.Address
   
8.     For i = 1 To Target.Count
   
9.         MsgBox "Target.Cells(" & i & ") ->" & Target.Cells(i).Address '=> MsgBox Target.(i).Address
   
10.     Next
    
11.     [B6].Select
    
12. End Sub

½Æ»s¥N½X