麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] excel VBA 自動更新篩選問題 [打印本頁]

作者: jsc0518 時間: 2021-7-21 09:03 標題: excel VBA 自動更新篩選問題

Dear all,

麻煩大家幫幫忙
我自己參考網路上的VBA語法寫了一段語法

想法是：
1.AF欄位有設定"資料驗證：待料,待生產,生產中,機台異常,結批,暫停"
[attach]33709[/attach]

2.AF欄位起初設定是將"結批"的選項勾選取消。
**** 此時AF欄位下並無資料有"機台異常"

3.若AF欄位下，選擇"結批"時，此時VBA會自動篩選掉

4.但AF欄位下，選擇"機台異常"時，結果也會被VBA會自動篩選掉

我要怎麼設定VBA參數
1.希望在AF欄位在做篩選時，之前勾選取消之項目會自動被篩選掉，
2.但之前未點選過的選項如設備異常，希望不要被框選取消。
[attach]33710[/attach]

[attach]33711[/attach]

作者: Andy2483 時間: 2021-7-21 15:03

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column = 32 And .Row >= 2 And .Count = 1 Then
   If ActiveSheet.FilterMode = True Then
      If .Value <> "機台異常" Then
         .Rows(.Count).EntireRow.Hidden = True
      End If
   End If
End If
End With
End Sub

參考!

作者: jsc0518 時間: 2021-7-21 15:44

回復 2# Andy2483
Dear Andy2483,
抱歉我表達的不清楚，

1.在AF欄位有設定"資料驗證(下拉式選單)：待料,待生產,生產中,機台異常,結批,暫停"這6個選項
2.在AF欄位下只有"待料,待生產,生產中,結批"這些資訊
3.我在AF欄位用篩選的方式，先把"結批"這取消勾選掉。
4.此時"結批"欄位會被自動隱藏掉。
5.這時我在AF欄位中，若點選下拉式選單選擇"機台異常"或"暫停"時，VBA一樣會把它們給予自動隱藏

我錄了一段過程，當我選擇"待料"VBA一樣會把它們給予自動隱藏
[attach]33713[/attach]

我發現大概是因為當我在做-->AF欄位用篩選的方式，先把"結批"這取消勾選掉時。
此時AF欄位下只有"生產中,待生產,暫停"這3個選項，
[attach]33714[/attach]
所以當我在AF選擇除了"生產中,待生產,暫停"這3個選項外的資訊，基本上都會被VBA自動隱藏

我是想說，有甚麼方式是可以讓VBA只認定我第一次取消的選項，而其他選項出來時，是無作動的

作者: n7822123 時間: 2021-7-21 23:47

本帖最後由 n7822123 於 2021-7-22 00:01 編輯

回復 3# jsc0518

我是想說，有甚麼方式是可以讓VBA只認定我第一次取消的選項，而其他選項出來時，是無作動的

篩選條件不會去記錄你 "取消" 的，只會記錄"現有" 的

如果你原本有 A、B、C ，3個條件，你把C取消了，Excel的篩選條件會紀錄成 "要A、也要B"，而不是 "不要C"

資料篩選的本意=從現有的資料去過濾

如果資料只有A、B、C，就算用VBA寫篩選條件，多一個D條件，Excel也是不認帳的

所以當資料有變動時，只能用VBA "重寫條件"

作者: Andy2483 時間: 2021-7-22 08:36

回復 3# jsc0518

2個以下篩選取消項有效
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
Dim Qx$, Af&, Sc$, i&
Af = 32
Sc = ""
Qx = ""
If .Column = Af And .Row >= 2 And .Count = 1 Then
   If ActiveSheet.FilterMode = True Then
      For i = 2 To ActiveSheet.UsedRange.Rows.Count
         If Rows(i).EntireRow.Hidden = True Then
            If Sc = "" Then
               Sc = Cells(i, "AF")
            End If
            If Qx = "" And Cells(i, "AF") <> Sc Then
               Qx = Cells(i, "AF")
            End If
            If InStr(Sc & "," & Qx, Cells(i, "AF")) = 0 Then
               GoTo 99
            End If
         End If
      Next
      Selection.AutoFilter Field:=Af, Criteria1:="<>" & Sc, Operator:=xlAnd, Criteria2:="<>" & Qx
      ActiveSheet.AutoFilter.ApplyFilter

99
   End If
End If
End With
End Sub

參考!

作者: jsc0518 時間: 2021-7-22 09:23

回復 5# Andy2483
Dear Andy2483,
早安！感謝您的回覆！
試過後可以使用，感恩！
可否教我一下VBA語法大概內容是怎麼做的呢？
謝謝你！！！

作者: jsc0518 時間: 2021-7-22 09:23

回復 4# n7822123
Dear n7822123,
早安您好，感謝您的回覆，知道囉！
Thank you.

作者: Andy2483 時間: 2021-7-22 10:33

回復 6# jsc0518

1.如果可以用! 代表方向對了!
2.Andy學很久了!進步很慢!這是用土方法拼湊出來的
2.1.只是用迴圈判斷篩選後隱藏了什麼項目
2.2.如果篩選後隱藏超過兩項就不適用!
如果兩項以下就以自訂篩選的方式取代取消勾選的篩選
3.應該有更高明的方法! 請高手前輩們指導!

作者: Andy2483 時間: 2021-7-22 11:52

回復 6# jsc0518

篩選1~5個項目均適用
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
Dim Qx$, Af&, Sh$, S1$, S2$, S3$, S4$, S5$, i&
Af = 32
Sh = ""
S1 = ""
S2 = ""
S3 = ""
S4 = ""
S5 = ""
If .Column = Af And .Row >= 2 And .Count = 1 Then
   If ActiveSheet.FilterMode = True Then
      For i = 2 To ActiveSheet.UsedRange.Rows.Count
         If Rows(i).EntireRow.Hidden = True Then
            If Sh = "" Then
               Sh = Cells(i, "AF")
               ElseIf InStr(Sh, Cells(i, "AF")) = 0 Then
                  Sh = Sh & "," & Cells(i, "AF")
            End If
            ElseIf InStr(Sh, Cells(i, "AF")) <> 0 Then
               GoTo 999
            ElseIf S1 = "" Then
               S1 = Cells(i, "AF")
            ElseIf S2 = "" And InStr(S1, Cells(i, "AF")) = 0 Then
               S2 = Cells(i, "AF")
            ElseIf S3 = "" And InStr(S1 & S2, Cells(i, "AF")) = 0 Then
               S3 = Cells(i, "AF")
            ElseIf S4 = "" And InStr(S1 & S2 & S3, Cells(i, "AF")) = 0 Then
               S4 = Cells(i, "AF")
            ElseIf S5 = "" And InStr(S1 & S2 & S3 & S4, Cells(i, "AF")) = 0 Then
               S5 = Cells(i, "AF")
               Exit For
         End If

999
      Next
      If InStr(Sh, .Value) <> 0 Then
         .Rows(.Count).EntireRow.Hidden = True
         Else
            Selection.AutoFilter Field:=Af, Criteria1:=Array( _
               S1, S2, S3, S4, S5), Operator:=xlFilterValues
            ActiveSheet.AutoFilter.ApplyFilter
      End If
   End If
End If
End With
End Sub

參考!

作者: jsc0518 時間: 2021-7-22 14:14

回復 8# Andy2483
請問一下，

If InStr(Sc & "," & Qx, Cells(i, "AF")) = 0 Then
GoTo 99 --> 這是甚麼用意、意思呢？

作者: jsc0518 時間: 2021-7-22 14:22

回復 9# Andy2483
Dear Andy2483,
剛剛測試使用OK，感恩囉。
請問一下，若項目超過5個以上的話，
是否修訂下列語法即可

Dim Qx$, Af&, Sh$, S1$, S2$, S3$, S4$, S5$, S6$, S7$, i&
Af = 32
Sh = ""
S1 = ""
S2 = ""
S3 = ""
S4 = ""
S5 = ""
S6 = ""
S7 = ""

作者: Andy2483 時間: 2021-7-22 14:50

回復 10# jsc0518

ElseIf InStr(Sh, Cells(i, "AF")) <> 0 Then
GoTo 999
如果儲存格Cells(i, "AF")是顯示的且在隱藏的儲存格裡有
就跳到 999繼續執行

作者: Andy2483 時間: 2021-7-22 14:58

回復 11# jsc0518

後面有關係到項目的都要類推EX:
ElseIf S6 = "" And InStr(S1 & S2 & S3 & S4 & S5, Cells(i, "AF")) = 0 Then
S6 = Cells(i, "AF")
~~~

多試幾次就會了!

作者: Andy2483 時間: 2021-7-22 15:32

回復 11# jsc0518

應該可以設計不限制項目數量!
但我不會! 靜待高手指導!

作者: jsc0518 時間: 2021-7-22 17:05

回復 12# Andy2483
Dear Andy2483,
感謝您的回覆與教導！

作者: jsc0518 時間: 2021-7-22 17:07

回復 13# Andy2483

了解囉，真的很感謝你的教導歐！

作者: Andy2483 時間: 2021-7-24 10:13

回復 16# jsc0518

'研究了3個帖子拼湊出了不限項目數量的方法
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
Dim Af&, Sh$, i&, Arr(), Brr(), K&, Dic As Object
Set Dic = CreateObject("scripting.dictionary")
Af = 32
If .Column = Af And .Row >= 2 And .Count = 1 Then
   If ActiveSheet.FilterMode = True Then
      Arr = Range("AF2:AF" & ActiveSheet.UsedRange.Rows.Count)
      ReDim Brr(1 To UBound(Arr), 1 To 1)
      For i = 1 To UBound(Arr)
         If Rows(i).EntireRow.Hidden = True Then
            If Sh = "" Then
               Sh = Cells(i, "AF")
               ElseIf InStr(Sh, Cells(i, "AF")) = 0 Then
                  Sh = Sh & "," & Cells(i, "AF")
            End If
            ElseIf InStr(Sh, Cells(i, "AF")) <> 0 Then
               GoTo 999
            ElseIf Dic.exists(Arr(i, 1)) Then
               Dic(Arr(i, 1)) = ""
               K = K + 1
               Brr(K, 1) = Arr(i, 1)
         End If

999
      Next
      If InStr(Sh, .Value) <> 0 Then
         .Rows(.Count).EntireRow.Hidden = True
         ElseIf K > 0 Then
            Selection.AutoFilter Field:=Af, Criteria1:=Brr, Operator:=xlFilterValues
            ActiveSheet.AutoFilter.ApplyFilter
      End If
   End If
End If
End With
End Sub
'猜測生產流程是待料>待生產>生產中>結批  特殊狀況機台異常or暫停以下經驗供參考
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
With Target
'在AF欄儲存格以滑鼠左鍵快按兩次>>依照流程改變流程進度
If .Column = 32 And .Row >= 2 Then
   If .Value = "待料" Then
      .Value = "待生產"
      ElseIf .Value = "待生產" Then
         .Value = "生產中"
      ElseIf .Value = "生產中" Then
         .Value = "結批"
      ElseIf .Value = "結批" Then
         MsgBox "?"
      ElseIf .Value = "機台異常" Then
         .Value = "生產中"
      ElseIf .Value = "暫停" Then
         .Value = "生產中"
   End If
   Cancel = True
End If
'在AG欄儲存格以滑鼠左鍵快按兩次>>改變流程特別狀況(機台異常)
If .Column = 33 And .Row >= 2 Then
   If .Cells(1, 0) = "生產中" Then
      .Cells(1, 0) = "機台異常"
   End If
   Cancel = True
End If
'在AF1儲存格以滑鼠左鍵快按兩次>>解除全部欄位篩選
If .Address = "$AF$1" Then
   If ActiveSheet.FilterMode = True Then
      ActiveSheet.ShowAllData
   End If
   Cancel = True
End If
End With
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
With Target
'在AF欄儲存格按滑鼠右鍵>>直接幫在AF欄篩選當格的項目
'EX: 在沒有篩選的情況下在AF2儲存格按滑鼠右鍵,如果AF2的文字是 "生產中" 就幫在AF欄篩選 "生產中"
If .Column = 32 And .Row >= 2 And .Count = 1 Then
   If ActiveSheet.FilterMode = True Then
      ActiveSheet.ShowAllData
   End If
   Selection.AutoFilter Field:=32, Criteria1:=.Value, Operator:=xlFilterValues
   Cancel = True
End If
'在AG欄儲存格按滑鼠右鍵>>改變流程特別狀況(暫停)
If .Column = 33 And .Row >= 2 Then
   If .Cells(1, 0) = "生產中" Then
      .Cells(1, 0) = "暫停"
   End If
   Cancel = True
End If
End With

參考!
End Sub

作者: jsc0518 時間: 2021-7-25 15:04

回復 17# Andy2483
Dear Andy2483,
下午好！感謝您的熱心回復歐，我再來測試看看！
Thank you so much. ^^

作者: n7822123 時間: 2021-7-25 21:33

本帖最後由 n7822123 於 2021-7-25 21:47 編輯

回復 18# jsc0518

這討論串還活著阿，看了一下，感覺搞複雜摟~~

只要想辦法記錄 "使用者不要的" 選項，再重新設定篩選條件就好了呀~

弄一個給你們參考看看吧，放棄原本手動用第一列篩選的功能，用表單篩選

1.任一欄點選兩下左鍵跳出表單
2.表單會自動抓第2列的 "資料篩選清單"，並秀出來給你選擇
3.把"要的"打勾，按確定即可完成篩選 (程式會自動記錄你"不要"的)
4.假設該欄資料沒有ItemA，但是清單有打勾，該欄多了一筆資料ItemA，也不會自動隱藏
5.支援擴充N欄，不限"AF"欄，但是若該欄沒有"資料篩選清單"，表單上不會有東西給你勾
6.設定條件可以紀錄到檔案關閉，亦即檔案關閉再開啟，需要重新設定
7.目前只支援1個表格，若要支援多表格，請自行擴充模組"儲存List"，取不同模組名稱即可
Ex:"表2儲存List"、"表3儲存List" ，如此才能記錄不同表格的篩選條件(不要的)
8.表單範圍大概支援30個選項，再多應該顯示不出來

[attach]33754[/attach]

作者: singo1232001 時間: 2021-7-26 00:12

本帖最後由 singo1232001 於 2021-7-26 00:16 編輯

回復 1# jsc0518

先說明一下
我寫的很心虛
可能會有一堆奇怪bug
就當作參考玩看看

我認為最有可能出現的bug 在於更之後的運用
也就是當你把真實資料放入
或者操作一些範例外的資料
可能就會產生未知bug
尤其是 af欄清除資料或者突然新增資料
資料記得要備份並且先行實際測試各種操作喔

另外裡面有一些累贅的程序我沒殺掉比如rd變數
因為我想說可能會有bug 不敢亂刪或者之後修改要用到

作者: n7822123 時間: 2021-7-26 00:37

本帖最後由 n7822123 於 2021-7-26 00:39 編輯

回復 20# singo1232001

哈，沒錯，好玩就好啦~

我也發現我剛剛的第7項說明不太妥當，紀錄條件用 "工作表的全域變數"比較好

隨著工作表就可擴充了 (該有的事件程式、全域變數要有)

因為主要是選不要的，篩選條件把空白納入(空白不過濾)

更新V2版本~~可自行擴充玩看看，雛形做到這樣就差不多嘍

1.任一欄點選兩下左鍵跳出表單
2.表單會自動抓該欄第2列的 "資料篩選清單"，並秀出來給你選擇
3.把"要的"打勾，按確定即可完成篩選 (程式會自動記錄你"不要"的)
4.假設該欄資料沒有ItemA，但是清單有打勾，該欄多了一筆資料ItemA，也不會自動隱藏
5.支援擴充N欄，"不限AF"欄，但是若該欄地2列沒有"資料篩選清單"，表單上不會有東西給你勾
6.設定條件可以紀錄到檔案關閉，亦即檔案關閉再開啟，需要重新設定
7.目前可支援2個表格，若有更多表格，請自行擴充工作表內的程式、變數即可~~
8.表單範圍大概支援秀出30個選項，再多應該顯示不出來，可自行修改表單 & 表單程式

[attach]33757[/attach]

作者: n7822123 時間: 2021-7-26 00:42

回復 20# singo1232001

你的我也玩一下，我把"生產中"取消篩選，再把AF欄位某一格選成 "生產中"

結果篩選條件又有"生產中"拉~~

作者: singo1232001 時間: 2021-7-26 04:39

本帖最後由 singo1232001 於 2021-7-26 04:51 編輯

回復 22# n7822123

我玩出一些新花樣
1.設置了一個自動篩選的觸發事件
2.直接找出自動篩選內的資料

順便補充全選.On=false 的時候內部沒有資料能查
貌似照原本的方式用字典去刪重複就可以

各位加油先戰到這裡愛睏了= =

作者: singo1232001 時間: 2021-7-26 14:25

回復 23# singo1232001

這是在網路上查到的
Application.AfterCalculate 觸發事件

https://stackoverflow.com/questions/67027407/excel-vba-instantiate-aftercalculate-event-handler-when-opening-workbook

做個簡單的範例給大家參考一下
不過要小心設置
避免連續觸發導致excel崩潰

作者: jsc0518 時間: 2021-7-26 20:42

回復 19# n7822123

Dear n7822123,
您好！感謝您的熱心回復與指導歐！
抱歉問題我問的略為複雜。
主要就是您所提到的：想辦法記錄 "使用者不要的" 選項
感恩囉！

作者: jsc0518 時間: 2021-7-26 20:43

回復 20# singo1232001
Dear singo1232001,
感謝您的回覆與指導歐！
我再來試試！
Thank you.

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