如何根据VBA用户表单中的列值隐藏列表框中的行

Question

我有一些记录显示在列表框中。我的用户表单中有一个"隐藏"按钮，单击后会隐藏所选行，该行仍保留在excel工作表中。但是，如果我再次打开用户表单，那么加载的数据将包含所有隐藏行。我创建了一个列"活动"，并为其分配了真值。当用户选择一行并单击隐藏时，我想将此值更改为false。我希望excel工作表中的所有数据都保留下来，并且只有"活动"值为True的行才会显示在列表框中。我该如何做到这一点？这是我的代码-

Sub Employee_Listbox()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("EMPMaster")

Dim last_row As Long
last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
If last_row = 1 Then last_row = 2

With Me.ListBox2
.ColumnCount = 6
.ColumnWidths = "150,70,100,50,70,0"
.List = sh.Range("A2:F" & last_row).Value  '.RowSource = sh.Name & "!A2:F" & last_row
End With

End Sub
Private Sub CommandButton15_Click() '''Hide button
If Me.ListBox2.ListIndex >= 0 Then
Me.ListBox2.RemoveItem Me.ListBox2.ListIndex
End If
End Sub

Answer 1

试试这个。它使用Match来定位源工作表上的行，并将该员工标记为"匹配"；不活动"；。

Option Explicit
Const MASTER_SHEET As String = "EMPMaster"
Const VAL_ACTIVE = "Active"
Const VAL_INACTIVE = "Inactive"
Private Sub UserForm_Activate()
LoadEmployeeList
End Sub

Sub LoadEmployeeList()
Dim sh As Worksheet, rw As Range, i As Long
Set sh = MasterSheet

With Me.ListBox2
.Clear
.ColumnCount = 6
.ColumnWidths = "150,70,100,50,70,0"
End With

For Each rw In sh.Range("A2:F" & sh.Cells(Rows.Count, "A").End(xlUp).Row).Rows
If rw.Columns("G").Value = VAL_ACTIVE Then 'only load "Active" employees
With Me.ListBox2
'add the row
.AddItem (rw.Cells(1).Value)
For i = 2 To rw.Cells.Count
.List(.ListCount - 1, i - 1) = rw.Cells(i).Value
Next i
End With
End If
Next rw
End Sub
Private Sub CommandButton15_Click() '''Hide button
Dim id, m, sh As Worksheet
If Me.ListBox2.ListIndex >= 0 Then
id = Me.ListBox2.List(Me.ListBox2.ListIndex, 0)    'adjust id column to suit
Set sh = MasterSheet
m = Application.Match(id, sh.Columns("A"), 0)      'find the id on the master sheet
If Not IsError(m) Then                             'found?
sh.Cells(m, "G").Value = "Inactive"            'mark as inactive
Me.ListBox2.RemoveItem Me.ListBox2.ListIndex   'remove from listbox
Else
'should not happen!
MsgBox "Employee Id '" & id & "' not found on Master sheet!", vbExclamation
End If
End If
End Sub
Function MasterSheet() As Worksheet
Set MasterSheet = ThisWorkbook.Worksheets(MASTER_SHEET)
End Function