I created a UserForm that enters data into a sheet. The form enters the data in cells A2:O2 and down. In my entry data UserForm, there is a button that displays a ListBox with every record entered. I want to filter this records by a specific column. The condition is: show every record that has that specific column blank.
Sub UpdateListBox()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Database")
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "O").End(xlUp).row
Dim i As Long, j As Long
Dim listItem As String
With frmOpenRecords.lstOpenRecords
.Clear
.ColumnCount = 15
.ColumnHeads = True
.ColumnWidths = "60; 80; 60; 100; 180; 100; 100; 250; 250; 65; 70; 80; 105; 100, 50"
.TextAlign = fmTextAlignLeft
.Font.Size = 10
End With
For i = 2 To lastRow
If ws.Cells(i, 14).Value = "" Then
frmOpenRecords.lstOpenRecords.RowSource = "Database!A2:N" & lastRow
End If
Next i
End Sub
The code above is what I have for now but it shows every record including the ones not blank in that specific column. Also, I have tried 3 different variations of code but none of them works. This is the only one that at least displays the ListBox. If somebody know how to fix this, please don’t hesitate to reply. Thankss!!
Jorge Omar David Gonzalez is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
6
You can try the following code, which lists all the data in sheet Database where Column N is empty.
Note that, the column headers will not be displayed on the ListBox automatically, so you need to use Label controls on top of the ListBox to display the headers.
Sub UpdateListBox()
Dim myFile As String, adoCN As Object, RS As Object, strSQL As String
Const adOpenKeyset = 1
myFile = ThisWorkbook.FullName
With frmOpenRecords.lstOpenRecords
.Clear
.ColumnCount = 15
.ColumnHeads = True
.ColumnWidths = "60; 80; 60; 100; 180; 100; 100; 250; 250; 65; 70; 80; 105; 100, 50"
.TextAlign = fmTextAlignLeft
.Font.Size = 10
End With
Set adoCN = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
adoCN.Provider = "Microsoft.ACE.OLEDB.12.0"
adoCN.Properties("Data Source") = myFile
adoCN.Properties("Extended Properties") = "Excel 12.0 Macro; HDR=No; IMEX=1"
adoCN.Open
strSQL = "Select * From [Database$] Where F14 Is Null"
RS.CursorType = adOpenKeyset
RS.Open strSQL, adoCN
frmOpenRecords.lstOpenRecords.Column = RS.GetRows(RS.RecordCount)
End Sub
5