Board logo

標題: [發問] 在vba userform 設3個 combox3 如何3層篩選 [打印本頁]
作者: user999    時間: 2011-10-28 07:54     標題: 在vba userform 設3個 combox3 如何3層篩選

在vba userform 設3個 combox3
資料來源 EXCEL SHEET1 分類:A欄 大項 B欄 小項C欄 細項  D欄 所要的值
這超個1層要如何寫,請教各位先進,謝謝!
作者: user999    時間: 2011-10-28 14:37

回復 1# user999 再
再麻煩您看看 我還沒權限 下載
作者: oobird    時間: 2011-10-28 15:35

userform中：
  1. Private Sub ComboBox1_Change()
  2. With ComboBox2
  3. If InStr(d(ComboBox1.Value), ",") = 0 Then
  4. .Clear
  5.         .AddItem d(ComboBox1.Value)
  6.         Else
  7. .List = Split(d(ComboBox1.Value), ",")
  8. End If
  9. End With
  10. End Sub

  12.          
  13. Private Sub ComboBox2_Change()
  14. With ComboBox3
  15. If InStr(d2(ComboBox1.Value & ComboBox2.Value), ",") = 0 Then
  16. .Clear
  17.         .AddItem d2(ComboBox1.Value & ComboBox2.Value)
  18.         Else
  19. .List = Split(d2(ComboBox1.Value & ComboBox2.Value), ",")
  20. End If
  21. End With

  23. End Sub

  25. Private Sub ComboBox3_Change()
  26. With Sheet1
  27. rng = .UsedRange
  28. For i = 2 To UBound(rng)
  29. If rng(i, 1) & rng(i, 2) & rng(i, 3) = ComboBox1.Value & ComboBox2.Value & ComboBox3.Value Then
  30. x = x + rng(i, 4)
  31. End If
  32. Next
  33. TextBox1.Value = x
  34. End With
  35. End Sub

  37. Private Sub UserForm_Initialize()
  38. Set d = CreateObject("Scripting.Dictionary")
  39. Set d2 = CreateObject("Scripting.Dictionary")
  40. With Sheet1
  41. rng = .UsedRange
  42. For i = 2 To UBound(rng)
  43. If d(rng(i, 1)) = "" Then
  44. d(rng(i, 1)) = rng(i, 2)
  45. Else
  46. If InStr(d(rng(i, 1)), rng(i, 2)) = 0 Then
  47. d(rng(i, 1)) = d(rng(i, 1)) & "," & rng(i, 2)
  48. End If
  49. End If
  50. If d2(rng(i, 1) & rng(i, 2)) = "" Then
  51. d2(rng(i, 1) & rng(i, 2)) = rng(i, 3)
  52. Else
  53. If InStr(d2(rng(i, 1) & rng(i, 2)), rng(i, 3)) = 0 Then
  54. d2(rng(i, 1) & rng(i, 2)) = d2(rng(i, 1) & rng(i, 2)) & "," & rng(i, 3)
  55. End If
  56. End If
  57. Next
  58. ComboBox1.List = d.keys
  59. End With
  60. End Sub
複製代碼
Module中
  1. Public d As Object
  2. Public d2 As Object
複製代碼
作者: GBKEE    時間: 2011-10-28 15:46

回復 2# user999
UserForm 的程式碼
  1. Dim D As Object
  2. Private Sub UserForm_Initialize()
  3.     Dim Dx(1 To 3) As Object, i As Integer, ii As Integer
  4.     Set D = CreateObject("SCRIPTING.DICTIONARY")
  5.     For ii = 1 To UBound(Dx)
  6.         Set Dx(ii) = CreateObject("SCRIPTING.DICTIONARY")
  7.     Next
  8.     i = 2
  9.     Do While Sheet1.Cells(i, 1) <> ""
  10.            For ii = 1 To UBound(Dx)
  11.                 Dx(ii)(Cells(i, ii).Value) = ""
  12.             Next
  13.             D(Cells(i, 1) & Cells(i, 2) & Cells(i, 3)) = Cells(i, "d")
  14.             i = i + 1
  15.     Loop
  16.     For ii = 1 To UBound(Dx)
  17.         Controls("ComboBox" & ii).List = Dx(ii).keys
  18.     Next
  19. End Sub
  20. Private Sub ComboBox1_Change()
  21.     平均
  22. End Sub
  23. Private Sub ComboBox2_Change()
  24.     平均
  25. End Sub
  26. Private Sub ComboBox3_Change()
  27.     平均
  28. End Sub
  29. Private Sub 平均()
  30.     If D.exists(ComboBox1 & ComboBox2 & ComboBox3) Then
  31.         TextBox1 = D(ComboBox1 & ComboBox2 & ComboBox3)
  32.     Else
  33.         TextBox1 = ""
  34.     End If
  35. End Sub
複製代碼
作者: user999    時間: 2011-10-28 17:23

回復 3# oobird


    太感謝您們了!我該好好學習,以您們為榜樣,熱心專業,謝謝!
作者: user999    時間: 2011-10-28 17:27

回復 4# GBKEE


     太感謝您們了!我該好好學習,以您們為榜樣,熱心專業,謝謝!



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)