[發問] 先不重複篩選後，並自動設為下拉式選單

iceandy6150

大家好，小弟目前有一工作表要填寫，如圖1

下載 (158.15 KB)

2020-5-16 00:03

也就是工作表1的第G、H、I欄
而填寫內容必須參照工作表2的表格，如圖2

下載 (161.67 KB)

2020-5-16 00:06

如果[用途類別(科目)]是--物品/消耗品
則[用途別] 必定是--業務費
且[工作計畫] 也必定是--公司業務

其實準則在[用途類別(科目)]

我的想法是
既然工作表1的第G、H、I欄，要慢慢打字，能否設定下拉式選單
而選單的內容值，是從工作表2去不重複篩選
這樣就可以用下拉式去選，不必打字
不重複篩選後如圖3

下載 (222.58 KB)

2020-5-16 00:13

當我在工作表1按下按鈕後
會自動把工作表1的G欄，設定下拉式清單，一次50列，來源是工作表2的F
會自動把工作表1的H欄，設定下拉式清單，一次50列，來源是工作表2的G
會自動把工作表1的I欄，設定下拉式清單，一次50列，來源是工作表2的H

但是，工作表2的這個表格，是隨時有可能增減的
每一次執行，都要能重新判斷，並把舊的篩選結果移除


【第二個自動化完美做法】
就是既然準則在[用途類別(科目)] --(工作表1的I欄)
那按下按鈕後
會自動把工作表1的I欄，設定下拉式清單，一次50列，來源是工作表2的H
然後I欄下拉選定某個值後
會自動把工作表1的H欄，填入工作表2對照表格中相對應的值
會自動把工作表1的G欄，填入工作表2對照表格中相對應的值
(連動的概念)


不論做法一 或 做法二(完美法)
我用錄製巨集的方式找出 不重複進階篩選並複製貼上到別的地方 的寫法
可是要讓那個範圍，設定為下拉式選單的參照範圍，尤其可能又是動態的
我一直搞不定
求助各位大大了，感謝

附上檔案
自動設下拉式選單.rar (23.9 KB)

下載次數: 9

2020-5-16 00:41

iceandy6150

回復 22# 准提部林


    感謝版主的教學回覆，我再來好好試試這幾個功能，感謝

准提部林

回復 21# iceandy6150


1)、為什麼IF的後面都不需要加上 END IF而且除錯都沒問題?
__if 條件 then 結果, 寫成一行, 就不須加 end if

2)、Target指的是滑鼠點到的任何一個儲存格嗎?
__就是selection中的range物件, 可以是單格, 選續範圍, 或不規則選取區塊

3)、我測試了一個小程式，但是只動了一次，就沒動作了，不曉得錯在哪裡?
__Application.EnableEvents = False
  "觸發事件"--關閉, 這是為了避免"重復觸發"造成無限循環,
  程式底下應再多一行:
  Application.EnableEvents = True
  (恢復觸發) 否則下次就沒作用!

4)、Set xA = .Cells(1, -1).Resize(1, 2) 這邊.Resize是什麼功能?
    以一個range定點, 向右或向下"擴展"成一個範圍,
  例如: range("a1").resize(4,3)  ---> A4:C4


=============================

iceandy6150

趁這個版面詢問一下，WorkSheet_Change的用法
底下分別是版主跟龍大的程式碼

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. Dim xF As Range, xA As Range
   
3. With Target
   
4.      If .Count > 1 Or .Columns.Count > 1 Or .Row < 2 Or .Column <> [I1].Column Then Exit Sub
   
5.      Set xA = .Cells(1, -1).Resize(1, 2): xA.ClearContents
   
6.      If .Value <> "" Then Set xF = [清單!d:d].Find(.Value, Lookat:=xlWhole)
   
7.      If Not xF Is Nothing Then xA = xF(1, -1).Resize(1, 2).Value
   
8. End With
   
9. End Sub
   
10. 
    
11. 
    
12. 
    
13. Private Sub Worksheet_Change(ByVal Target As Range)
    
14. 
    
15. If Target.Column <> 9 Then Exit Sub
    
16. 
    
17. Application.EnableEvents = False
    
18. 塞資料入字典
    
19. Key$ = Target.Value
    
20. Cells(Target.Row, 8) = 下拉選單.D(Key).用途別
    
21. Cells(Target.Row, 7) = 下拉選單.D(Key).工作計畫
    
22. Application.EnableEvents = True
    
23. 
    
24. End Sub

複製代碼

我的問題是
一、為什麼IF的後面都不需要加上 END IF而且除錯都沒問題

二、Target指的是滑鼠點到的任何一個儲存格嗎?   因為可以取得Target.Row
       或是Target.Column。
      但是Private Sub Worksheet_Change(ByVal Target As Range)又把Target當RANGE ?

三、我測試了一個小程式，但是只動了一次，就沒動作了，不曉得錯在哪裡

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. If Target.Column <> 3 Then Exit Sub
   
3. Application.EnableEvents = False
   
4. Target.Interior.ColorIndex = 3
   
5. End Sub

複製代碼

照理說，我隨便選一個儲存格，如果該儲存格不是第3欄 (C欄)
     那就離開程式
     如果選到其他欄，那就會把儲存格顏色變紅色
     但是就沒辦法
     就算我選到的儲存格，隨便輸入123，再刪掉，這樣算是有Change了吧?
     也沒動作....

四、Set xA = .Cells(1, -1).Resize(1, 2): xA.ClearContents 這邊.Resize是什麼功能?

以上詢問，謝謝

iceandy6150

回復 15# jackyq


    大大，您說的功能我都不會使用，如果有機會，再請您教學一下，謝謝

iceandy6150

回復 18# 准提部林


    感謝版主回覆教學，看來也是利用Sheet_Change去做處理
    這邊我再研究一下使用方式，謝謝

准提部林

回復 13# iceandy6150


比如，A選到3大類，B選3中類，C卻選去1小類---(這樣帳的科目就錯了)  
_2樓的檔案, 不會有這問題
_未選取第一層, 不會准許選第二層; 選了第一層, 自動幫你篩選出符合第一層的清單,
續選第二層(若第二層空白, 可不用選)後, 再過瀘出第三層, 可以說下拉清單會越來越少,
絕對不會有錯選的問題~~
_至于"清單"來源表, 可不限制往下新增, 只要將公式下拉到底, 會自動判斷清單範圍, 這部份還不用VBA

工作表一用到的VBA:
1) 清空輸入資料(若已將原有內容貼至別處), 並重置下拉清單50行

如果第三層清單不重覆(附檔中有兩個"電費", 修改)
Xl0000170(三層下拉清單)V2.rar (18.12 KB)

下載次數: 10

2020-5-22 15:42

iceandy6150

回復 15# jackyq

聽起來是挺深的，有時間再來搜尋看看這些用法，感謝您的分享與教學

iceandy6150

回復 14# n7822123

感謝您的教學，原來用照片也可以做同樣的功能，又學到一招
也感謝分享網址，會多去看看的

jackyq

你要的功能想要弄到最容易使用 ( List 易搜 , 支持 mousescroll )

就是 把另一個 EXCEL 嵌入到 本體 EXCEL

另一個 EXCEL 直接拿來當 List

直接支援 mousescroll ,  綜觀查找,  也可篩選查找

ListBox 最大問題:  No mousescroll , 東西一多要找到東西有點費力