I have some records being displayed in a listbox. I want to be able to click on a record in the listbox and the click on a button to hide it. I do not want the row to be deleted from the excel sheet. How can I do this?
Hi @RoryA ,Don't link the listbox to a range directly. Populate the control using an array, then you can remove items as you need to.
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
.ColumnHeads = True
.ColumnCount = 6
.ColumnWidths = "150,70,100,50,70,0"
.RowSource = sh.Name & "!A2:F" & last_row
End With
End Sub
.RowSource = sh.Name & "!A2:F" & last_row
.List = sh.Range("A2:F" & last_row).Value
Thank you so much! This works! I am not getting the headers anymore though. Should I create a separate listbox for the header or can it be done in the code that I have already provided?Change this:
Code:.RowSource = sh.Name & "!A2:F" & last_row
to this:
Code:.List = sh.Range("A2:F" & last_row).Value
You can then use removeitem in your other code without having to delete the actual data on the sheet.
I see. I will use another listbox for this.That is the trade-off. You can only use automatic column headers if the source data is in a range so you need to work around that in whatever way suits your setup. (could be another listbox; labels above the listbox; using the first row as headers)
This works but when I reload the userform the hidden rows get loaded as well into the listbox. Is there a way to stop hidden rows from getting displayed every time the userform is opened?Change this:
Code:.RowSource = sh.Name & "!A2:F" & last_row
to this:
Code:.List = sh.Range("A2:F" & last_row).Value
You can then use removeitem in your other code without having to delete the actual data on the sheet.
Sure. It would be of great help.You will have to loop through the range and only put the visible cells into an array. Give me a few minutes and I'll come up with some code for that.