CODEWITHSUNDEEP
excelvbauserform
Pete
Pete

Reputation: 449

How can I hide row in list box based on a column value in VBA userform?

I have some records being displayed in a listbox. I have a 'Hide' button in my userform which when clicked hides the row selected and the row remains in the excel sheet. But if I open the userform again then the data loaded has all the hidden rows. I have created a column 'Active' and assigned it true values. I want to change this value to false when the user selects a row and clicks on hide. I want all the data in the excel sheet to remain and only rows with 'Active' value True will be displayed in the listbox. How do I achieve this? Here is my code -

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

Upvotes: 0

Views: 1045

Answers (1)

Tim Williams
Tim Williams

Reputation: 166860

Try this out. It uses Match to locate the row on the source worksheet and flag that employee as "Inactive".

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

Upvotes: 0

Related Questions