- 帖子
- 1447
- 主題
- 40
- 精華
- 0
- 積分
- 1471
- 點名
- 0
- 作業系統
- Windows 7
- 軟體版本
- Excel 2010 & 2016
- 閱讀權限
- 50
- 性別
- 男
- 來自
- 台灣
- 註冊時間
- 2020-7-15
- 最後登錄
- 2024-12-5
|
17#
發表於 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 |
|
