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

¥i¥H§Q¥ÎVBA¶i¦æ¦h±ø¥ó¿z¿ï¶Ü?

¥i¥H§Q¥ÎVBA¶i¦æ¦h±ø¥ó¿z¿ï¶Ü?

·Q½Ð°Ý¤j¤j
ÁöµMOffice 2007¦³¤º«Ø¿z¿ï¥\¯à
¦ý¦pªG§Ú·Q­n±N¿z¿ï¥\¯à¼g¦b¤@­Óªí³æ¸Ì­±
¥i¥H¤@¦¸¶i¦æ¦h±ø¥ó¿z¿ï¸ê®Æ¶Ü?(¦p¹Ï)


¦]¬°¥Ø«e§Ú¥u¦³¼g¤@¦¸¿é¤J³æµ§¸ê®Æ¥H©Ê¦W¬°·j´M¥Ø¼Ð
µ{¦¡¹B¦æµ²ªG¦p¹Ï


¦ý¦pªG§Ú·Q­n±N·j´M±ø¥ó¬°¨âµ§¥H¤W(¼Ò½k·j´M)
¬Æ¦Ü¨ì§¹¥þ²Å¦X
±N¿z¿ïµ²ªGÅã¥Ü¦b­ì¨Óªºªí³æ(¥¼²Å¦X±ø¥óªº¸ê®ÆÁôÂÃ(§éÅ|°_¨Ó))
³o¼Ëªº¼gªk´N¨S¿ìªk¤F:Q
¥Ø«eµ{¦¡¼gªk¦p¤U(¶¶±aªþ¤Wªí³æ)
´ú¸ÕÀÉ(¿z¿ï±ø¥ó).zip (53.77 KB)

§Æ±æ¦³¤j¤j¯à¸Ñ¨M§ÚªººÃ´b>"<
  1. Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

  2. With Sheet2

  3. Set a = .Columns("A").Find(TextBox1.Text, lookat:=xlWhole, LookIn:=xlValues)
  4. If a Is Nothing Then
  5. MsgBox "¬dµL¦¹¡A½Ð½T»{«á¦A¿é¤J¡I"

  6. Exit Sub
  7. End If

  8. ComboBox1.Value = a.Offset(, 1) '¦~°O
  9. ComboBox2.Value = a.Offset(, 2) '¨­°ª
  10. ComboBox3.Value = a.Offset(, 3) 'Åé­«
  11. TextBox2.Value = a.Offset(, 4) '¦í§}


  12. End With
  13. End Sub
  14. Private Sub CommandButton2_Click()
  15. Me.ComboBox1 = ""
  16. Me.ComboBox2 = ""
  17. Me.ComboBox3 = ""
  18. Me.TextBox1 = ""
  19. Me.TextBox2 = ""
  20. End Sub

  21. Private Sub CommandButton3_Click()
  22. Test5.Hide
  23. End Sub

  24. Private Sub UserForm_Initialize() '¤@¶}©lªº³]©w
  25. Dim i
  26. For i = 10 To 18
  27.     ComboBox1.AddItem i
  28. Next

  29. For i = 120 To 160
  30.     ComboBox2.AddItem i
  31. Next

  32. For i = 30 To 70
  33.     ComboBox3.AddItem i
  34. Next
  35. End Sub
½Æ»s¥N½X
Jared

¦^´_ 1# Jared


µo²{§Q¥Î³°»s¥¨¶°ªº¤è¦¡
¥i¥H¤@¦¸¿z¿ï¤@­Ó±ø¥ó
¨º¦pªG¬O­n¨â­Ó¥H¤W
­n«ç»ò³]©w©O?
  1. Private Sub CommandButton4_Click() '¿z¿ï±ø¥ó
  2. Dim a, b, c, d, e
  3. a = Test5.TextBox1.Text
  4. b = Test5.ComboBox1.Value
  5. c = Test5.ComboBox2.Value
  6. d = Test5.ComboBox3.Value
  7. e = Test5.TextBox2.Text
  8. If a <> "" Then
  9. ActiveSheet.Range("$A$1:$E$12").AutoFilter Field:=1, Criteria1:=a: Exit Sub
  10. End If

  11. If b <> "" Then
  12. ActiveSheet.Range("$A$1:$E$12").AutoFilter Field:=2, Criteria1:=b: Exit Sub
  13. End If

  14. If c <> "" Then
  15. ActiveSheet.Range("$A$1:$E$12").AutoFilter Field:=3, Criteria1:=c: Exit Sub
  16. End If

  17. If d <> "" Then
  18. ActiveSheet.Range("$A$1:$E$12").AutoFilter Field:=4, Criteria1:=d: Exit Sub
  19. End If

  20. If e <> "" Then
  21. ActiveSheet.Range("$A$1:$E$12").AutoFilter Field:=5, Criteria1:=e: Exit Sub
  22. End If
  23. End Sub
½Æ»s¥N½X
Jared

TOP

¦^´_ 2# Jared
  1. Option Explicit
  2. Private Sub CommandButton4_Click() '¿z¿ï±ø¥ó
  3. Dim a, b, c, d, e
  4. a = Test5.TextBox1.Text
  5. b = Test5.ComboBox1.Value
  6. c = Test5.ComboBox2.Value
  7. d = Test5.ComboBox3.Value
  8. e = Test5.TextBox2.Text
  9. With ActiveSheet.Range("$A$1:$E$12")
  10.     .Parent.AutoFilterMode = False   'Åã¥Ü¥þ³¡¸ê®Æ ->·sªº ¦h­«¿z¿ï ¤~·|½T.
  11.     '¦h­«¿z¿ï  ..........
  12.     If a <> "" Then .AutoFilter Field:=1, Criteria1:=a
  13.     If b <> "" Then .AutoFilter Field:=2, Criteria1:=b
  14.     If c <> "" Then .AutoFilter Field:=3, Criteria1:=c
  15.     If d <> "" Then .AutoFilter Field:=4, Criteria1:=d
  16.     If e <> "" Then .AutoFilter Field:=5, Criteria1:=e
  17.     '¦h­«¿z¿ï  ..........
  18. End With
  19. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 3# GBKEE


·PÁ¤j¤j¸Ñ°£¤F§ÚªººÃ´b
­ì¥»ªºµ{¦¡½X¬O¼g¦¨¿W¥ß¦¡
  1. If a <> "" Then
  2. ActiveSheet.Range("$A$1:$E$12").AutoFilter Field:=1, Criteria1:=a: Exit Sub
  3. End If
½Æ»s¥N½X
«á¨Ó¦³­×§ï¦¨
  1. Private Sub CommandButton4_Click()
  2. Dim a, b, c, d, e
  3. a = Test5.TextBox1.Text
  4. b = Test5.ComboBox1.Value
  5. c = Test5.ComboBox2.Value
  6. d = Test5.ComboBox3.Value
  7. e = Test5.TextBox2.Text

  8. If a <> "" Or b <> "" Or c <> "" Or d <> "" Or e <> "" Then
  9. With Sheet2.Range("$A$1:$E$12")
  10. .AutoFilter Field:=1, Criteria1:=a
  11. .AutoFilter Field:=2, Criteria1:=b
  12. .AutoFilter Field:=3, Criteria1:=c
  13. .AutoFilter Field:=4, Criteria1:=d
  14. .AutoFilter Field:=5, Criteria1:=e

  15. End With
  16. End If
  17. End Sub
½Æ»s¥N½X
¦ý«oÅܦ¨­n¨C­Ó±ø¥ó³£²Å¦X¤~·|¿z¿ï¥XªF¦è>"<
¬Ý¤F¤j¤jªº¸Ñµª
­ì¨Ó¬O­n¤À¶}¶i¦æ

¬Ý¨Ó§Ú¤S¾Ç¨ì¤@½Ò¤F^¤f^
«D±`·PÁ¡I¡I
Jared

TOP

2¦ì¤j¤j¡A¤p§Ì­è¦b¾Ç²ßVBA¡A¥i¥H±N±z­Ì°Q½×ªºµ²ªG¡AÀɮפW¶Ç¨Ñ§Ú­Ì¤U¸ü¶Ü?·P®¦

TOP

¦^´_ 5# jeffrey628litw


§A¥i¥H§â¼Ó³»°ÝÃDªº´ú¸ÕÀɤU¸ü¤U¨Ó
¦b§âµ{¦¡½X´À´«±¼´N¥i¥H¤F
¤¶­±¤WÀ³¸Ó¬O¨S¤°»ò§ï¨ì
¥u¦³¦bµ{¦¡½Xªº³¡¤À
Jared

TOP

ÁÂÁ¤À¨É¡C

TOP

¦^´_ 4# Jared


   a = Test5.TextBox1.Text
b = Test5.ComboBox1.Value
c = Test5.ComboBox2.Value
d = Test5.ComboBox3.Value¡C¤j¤j½Ð°Ýtest5¡C³o­Ó¦a¤è¬O«ç»ò³]­pªº¡C¤p§ÌÅv­­¤£¨¬¤£¯à¤U¸ü¨Ó¬Ý¡C©êºp

TOP

¦^´_ 3# GBKEE


    µo²{³o¬O´X¦~«eªº©«¤l¤F¡C´«°Ý°Ýª©¥D¡C
a = Test5.TextBox1.Text
b = Test5.ComboBox1.Value
c = Test5.ComboBox2.Value
d = Test5.ComboBox3.Value¡C¤j¤j½Ð°Ýtest5¡C³o­Ó¦a¤è¬O«ç»ò³]­pªº¡C¤p§ÌÅv­­¤£¨¬¤£¯à¤U¸ü¨Ó¬Ý¡C©êºp

TOP

¥»©«³Ì«á¥Ñ Michelle-W ©ó 2017-7-20 09:13 ½s¿è

¦^´_ 3# GBKEE

ª©¥D¤j¤j±z¦n¡A
§Úªº¦h±ø¥ó¿z¿ï¥\¯àÁ`¬O¥¢±Ñ...¥i§_¨ó§UÀ°¦£¬Ý¬Ý­þÃä¿ù¤F
¦Ó¥BÀ³¸Ó¦p¦ó±N¿z¿ï§¹ªº¸ê®Æ¶K¨ì¤¶­±¤W(¨Ì¾Ú¤£¦PÄ檺¦WºÙ)
¥ýÁÂÁ±z¤F!
ºÃ°Ý..rar (34.68 KB)

TOP

        ÀR«ä¦Û¦b : §g¤l¦p¤ô¡AÀH¤è´N¶ê¡AµL³B¤£¦Û¦b¡C
ªð¦^¦Cªí ¤W¤@¥DÃD