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

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

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

¤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

ºû­×³æ.rar (7.03 KB)

robin

¥»©«³Ì«á¥Ñ iceandy6150 ©ó 2014-2-8 23:11 ½s¿è

¦^´_ 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
«¢Åo~¤j®a¦n§r

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-2-9 07:34 ½s¿è

¦^´_ 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. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  22.     'SelectionChange :¤u§@ªíÀx¦s®æ ¦³²¾°Ê®É©ÒIJµoªºµ{§Ç
  23.     If Not Application.Intersect(Range("a:a"), Target) Is Nothing Then  'Àx¦s®æ²¾°Ê¨ìAÄæ®É
  24.         Range("a:a").Validation.Delete
  25.         Target(1).Validation.Add xlValidateList, , , "«O¤º,¤G­×,«O¥~,¤H¬°"
  26.         'Validation ª«¥ó¡A¸Óª«¥ó¥Nªí«ü©w½d³ò¤ºªº¸ê®ÆÅçÃÒ(¿é¤Jªº¸ê®Æ­n²Å¦X«ü©wªº¸ê®Æ)
  27.         'AÄ榳¤U©Ô¿ï³æ¡A¤À§O¬O¡G«O¤º¡B¤G­×¡B«O¥~¡B¤H¬°
  28.     End If
  29. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

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

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-2-9 18:34 ½s¿è

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

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

TOP

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

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2014-2-9 19:25 ½s¿è

¦^´_ 1# newlink
  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. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  12. If Target(1).Column = 1 Then '¿ï¨úAÄæ
  13. With Target(1).Validation '³]©wÅçÃÒ²M³æ
  14.   .Delete
  15.    .Add xlValidateList, , , "«O¤º,¤G­×,«O¥~,¤H¬°"
  16. End With
  17. End If
  18. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

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

TOP

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : §Ú­Ì­n°µ¦nªÀ·|ªºÀô«O¡A¤]­n°µ¦n¤º¤ßªºÀô«O¡C
ªð¦^¦Cªí ¤W¤@¥DÃD