我正在嘗試根據表中的過濾器更改用戶表單中命令按鈕的背景顏色。
我如何檢查自動過濾器的結果是否存在以及結果是否正確。CommandButton2 有標題 Dummy 4，表的結果將為空。
Private Sub UserForm_Initialize()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = ActiveSheet
Dim tbl As ListObject: Set tbl = ws.ListObjects("table1")
With tbl
.Range.AutoFilter Field:=1, Criteria1:=ws.Range("E2")
.Range.AutoFilter Field:=2, Criteria1:=ws.Range("F2")
.Range.AutoFilter Field:=3, Criteria1:=ws.Range("G2")
End With
'if table is empty make the button red
CommandButton1.BackColor = RGB(0, 255, 0)
CommandButton2.BackColor = RGB(255, 0, 0)
End Sub
我已經嘗試過，isempty()但無法得到正確的結果。
編輯：得到這個
Private Sub UserForm_Initialize()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = ActiveSheet
Dim tbl As ListObject: Set tbl = ws.ListObjects("table1")
With tbl
.Range.AutoFilter Field:=1, Criteria1:=ws.Range("E2")
.Range.AutoFilter Field:=2, Criteria1:=ws.Range("F2")
.Range.AutoFilter Field:=3, Criteria1:=ws.Range("G2")
End With
On Error Resume Next
tbl.DataBodyRange.SpecialCells (xlCellTypeVisible)
If Err = 0 Then
CommandButton1.BackColor = RGB(0, 255, 0)
Else
CommandButton1.BackColor = RGB(255, 0, 0)
End If
End Sub
uj5u.com熱心網友回復：
測驗 Excel 表格過濾結果
Option Explicit
Private Sub UserForm_Initialize()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = ActiveSheet
Dim tbl As ListObject: Set tbl = ws.ListObjects("Table1")
Dim rg As Range
With tbl
.Range.AutoFilter Field:=1, Criteria1:=ws.Range("E2")
.Range.AutoFilter Field:=2, Criteria1:=ws.Range("F2")
.Range.AutoFilter Field:=3, Criteria1:=ws.Range("G2")
On Error Resume Next
Set rg = .DataBodyRange.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rg Is Nothing Then
CommandButton1.BackColor = RGB(0, 255, 0)
CommandButton2.BackColor = RGB(255, 0, 0)
Else
CommandButton1.BackColor = RGB(255, 0, 0)
CommandButton2.BackColor = RGB(0, 255, 0)
End If
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/335703.html